Problem in Insertion in SQL SERVER using String Builder

Question

0.00/5 (No votes)

See more:

I am unable to insert in database using the following code :

C#

public static int Insert(string query)
       {
           int i=0;
           try
           {
               DBConnection.InitializeConnection(cmd);
               cmd.CommandText = query;
               cmd.CommandType = CommandType.Text;
               if (cmd.Connection.State != ConnectionState.Open)
               {
                   cmd.Connection.Open();
                   i = cmd.ExecuteNonQuery();
               }
           }
           catch (Exception)
           {
               i = 0;
           }
           finally
           {
               if (cmd.Connection.State != ConnectionState.Closed)
               {
                   cmd.Connection.Close();
               }
          }
           return i;
       }

C#

try
{
query.Append("INSERT INTO Suppliers ");
query.Append("(SupName,ConcernedPerson,MobileNum,PostalAddr,Email) ");
query.Append("VALUES("+txtSupplier.Text.ToString());
query.Append(","+txtConcerned.Text.ToString());
query.Append(","+Convert.ToInt64(txtMobile.Text));
query.Append(","+txtAddress.Text.ToString());
query.Append(","+txtEmail.Text.ToString()+");");
int i=POS.DbOperation.Insert(query.ToString());
MessageBox.Show(i.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex+""+ex.InnerException);
}

Is this method perfect to do insertion over any other method?
Thanks,
Hritik

Posted 10-Sep-14 22:19pm

Akkywadhwa

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-09-10T22:23:00

Solution 1

NO!
It's wide open to SQL Injection attack which can damage or destroy your database. Use a parametrised query instead!

Posted 10-Sep-14 22:23pm

OriginalGriff

Pheonyx · Answer 2 · 2014-09-10T22:31:00

Solution 2

This is not a good approach to inserting data into a database as it is highly vulnerable, especially to SQL Injection attacks.

However, to get your code to work all of your string need to be within single quotes, which looking at your code appears not to be the case.

If you changed it to:

C#

query.Append("INSERT INTO Suppliers ");
query.Append("(SupName,ConcernedPerson,MobileNum,PostalAddr,Email) ");
query.Append("VALUES('"+txtSupplier.Text.ToString());
query.Append("','"+txtConcerned.Text.ToString());
query.Append("','"+Convert.ToInt64(txtMobile.Text));
query.Append("','"+txtAddress.Text.ToString());
query.Append("','"+txtEmail.Text.ToString()+"');");

Then it should work.

However I would suggest you look at using Parameters.

If you changed your Insert method to have this definition:

public static int Insert(string query, Dictionary<string,> parameters)

Then instead of how you generate your query string you could have:

C#

var insertQuery = @"INSERT INTO Suppliers (SupName, ConcernedPerson, MobileNum, PostalAddr, Email) Values (@name, @concernedPerson, @mobile, @addr, @email)

Then create you parameters dictionary as follows:

C#

var paramDic = new Dictionary<string,>();
paramDic.Add("@name", txtSupplier.Text); //Repeat this line for each parameter

Call your new insert method as
Insert(insertQuery, paramDic)

Then you need to tweak your insert command as follows:

C#

cmd.CommandText = query;
foreach(var p in parameters){
cmd.Parameters.AddWithValue(p.Key, p.Value);
}

Posted 10-Sep-14 22:31pm

Pheonyx

Comments

Akkywadhwa 11-Sep-14 4:53am

public static int Insert(string query,Dictionary<string,string> parameters)
{
int i=0;
try
{
DBConnection.InitializeConnection(cmd);
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.Key,p.Value);
}
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
i = cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
i = 0;
}
finally
{
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
return i;
}

query = "INSERT INTO Suppliers ";
query+="(SupName,ConcernedPerson,MobileNum,PostalAddr,Email) ";
query += "VALUES(@Name,@ConcernedPerson,@Mobile,@Postal,@Email)";
var param = new Dictionary<string,string>();
param.Add("@Name", txtSupplier.Text.ToString());
param.Add("@ConcernedPerson", txtConcerned.Text.ToString());
param.Add("@Mobile", txtMobile.Text.ToString());
param.Add("@Postal", txtAddress.Text.ToString());
param.Add("@Email", txtEmail.Text.ToString());
int i= POS.DbOperation.Insert(query, param);
MessageBox.Show(i.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex+""+ex.InnerException);
}
}

This too doesn't work.
Hritik

Pheonyx 11-Sep-14 5:11am

What error are you getting?

Akkywadhwa 11-Sep-14 6:03am

Object reference not set to an instance of an object.
System Null Reference Exception

Pheonyx 11-Sep-14 6:04am

Have you debugged your code to see where the error is occurring, on what line. You should do that and see which object is null.

Akkywadhwa 11-Sep-14 11:18am

Its on line "int i= POS.DbOperation.Insert(query, param);"

Pheonyx 11-Sep-14 11:28am

And are any of the variables null? i.e. query or param?
If not, is POS null? or POS.DbOperation ?

George Jonsson · Answer 3 · 2014-09-10T22:32:00

Solution 3

You should look into DbCommand[^] and DbParameter[^] instead.
It is much safer than using concatenated strings.

Configuring Parameters and Parameter Data Types[^]

Posted 10-Sep-14 22:32pm

George Jonsson