Hey,
I learned parametrized query in ASP.net C#.
The following link was very helpful.
http://geekswithblogs.net/dotNETvinz/archive/2009/04/30/creating-a-simple-registration-form-in-asp.net.aspx
Adding part of code for your quick reference.
The code is basically a registration form:
private void ExecuteInsert(string name, string username, string password, string gender, string age, string address)
{
SqlConnection conn = new SqlConnection(GetConnectionString());
string sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age) VALUES "
+ " (@Name,@UserName,@Password,@Gender,@Age)";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
param[2] = new SqlParameter("@Password", SqlDbType.VarChar, 50);
param[3] = new SqlParameter("@Gender", SqlDbType.Char, 10);
param[4] = new SqlParameter("@Age", SqlDbType.Int, 100);
param[0].Value = name;
param[1].Value = username;
param[2].Value = password;
param[3].Value = gender;
param[4].Value = age;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
The code works perfect if we are using MSSQL DB Server.
But in case you are using Mysql DB server then you need to modify the code little bit as follows:
private void ExecuteInsert(string name, string username, string password, string gender, string age)
{
MySqlConnection conn = new MySqlConnection(GetConnectionString());
string sql = "INSERT INTO test_user (first_nm, last_nm, pword, age ,gender) VALUES "
+ "(?Name,?UserName,?Password,?Age,?Gender)";
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlParameter[] param = new MySqlParameter[5];
param[0] = new MySqlParameter("?Name", MySqlDbType.VarChar, 50);
param[1] = new MySqlParameter("?UserName", MySqlDbType.VarChar, 50);
param[2] = new MySqlParameter("?Password", MySqlDbType.VarChar, 50);
param[3] = new MySqlParameter("?Age", MySqlDbType.VarChar, 12);
param[4] = new MySqlParameter("?Gender", MySqlDbType.VarChar, 6);
param[0].Value = name;
param[1].Value = username;
param[2].Value = password;
param[3].Value = age;
param[4].Value = gender;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
//Response.Write(CommandType.Text+ "
");
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
This example shows you how to run a parameterized query against MySQL.
The trick is to remember the ? instad of @ – which is the case of SQL Server.
Recent Comments