Parameterizing an SQL query in C#

[TagaBicol]

Guys gusto ko lng itanong kung ito yung tamang pag parameterize ng sql query o pwede pang eenhance. thanks guys

here is the code

 private void SaveRecord()
        {
            string connString = ConfigurationManager.AppSettings["connString"];
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO Profiles (name, age, gender, address, [contact no.]) VALUES (@fname + ' ' + @mName + ' ' + @sName, @age, @gender, @address, @contactNo)", conn);
                cmd.Parameters.AddWithValue("@fname", txtfName.Text);
                cmd.Parameters.AddWithValue("@mName", txtMName.Text);
                cmd.Parameters.AddWithValue("@sName", txtSName.Text);
                cmd.Parameters.AddWithValue("@age", cmbAge.Text);
                cmd.Parameters.AddWithValue("@gender", cmbGender.Text);
                cmd.Parameters.AddWithValue("@address", txtAddress.Text);
                cmd.Parameters.AddWithValue("@contactNo", Convert.ToInt32(txtContactNum.Text));

                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    MessageBox.Show("New Profile Saved");
                }
                else
                {
                    MessageBox.Show("Profile not saved");
                }
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

 

[blueclown25]

Re: Parameterizing an SQL query

Guys gusto ko lng itanong kung ito yung tamang pag parameterize ng sql query o pwede pang eenhance. thanks guys

here is the code

 private void SaveRecord()
        {
            string connString = ConfigurationManager.AppSettings["connString"];
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO Profiles (name, age, gender, address, [contact no.]) VALUES (@fname + ' ' + @mName + ' ' + @sName, @age, @gender, @address, @contactNo)", conn);
                cmd.Parameters.AddWithValue("@fname", txtfName.Text);
                cmd.Parameters.AddWithValue("@mName", txtMName.Text);
                cmd.Parameters.AddWithValue("@sName", txtSName.Text);
                cmd.Parameters.AddWithValue("@age", cmbAge.Text);
                cmd.Parameters.AddWithValue("@gender", cmbGender.Text);
                cmd.Parameters.AddWithValue("@address", txtAddress.Text);
                cmd.Parameters.AddWithValue("@contactNo", Convert.ToInt32(txtContactNum.Text));

                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    MessageBox.Show("New Profile Saved");
                }
                else
                {
                    MessageBox.Show("Profile not saved");
                }
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

ts, ako in my personal exp medyo bihira ko gumamit ng parameters.addwithvalue madalas kasi ganto ginagawa ko e.

ex:

string sqlquery = string.format("Insert into tablename (column1(INT), column2(STRING)) values ({0},'{1}')",value1,value2);

pero tingin ko mas malinis tignan yung may parameters kaso hindi ko na masyado nagagamit yung gnyan dahil sa entityframework.

 

[xAinTKroSs]

Re: Parameterizing an SQL query

tama. At mas maganda ang pag parameters iwas SQL injection rin. Tapos Para di ka rin mahirapan pwede ung

VALUES (?, ?, ?, ?, ?) - Depende ung "?" sa bilang ng columns

 

[lymcrest16]

Re: Parameterizing an SQL query

gumamit ka na lang ng stored procedures sir

okay din yung paggamit ng string.Format() ganun din kasi ginagamit ko since puro console apps madalas ko ginagawa ..
kung sa web ka naman magstored procedures ka na lang sir.

 

[blueclown25]

Re: Parameterizing an SQL query

Sir Lymcrest, question lang kung madali bang ma sql injection ang string.format? kesa sa parameters?

 

[bps.xcs001]

Re: Parameterizing an SQL query

IF this is a professional system, I suggest using stored procedure instead of what you've done..

 

[lymcrest16]

Re: Parameterizing an SQL query

sir okay ang string.Format() basta yung inputs mo eh nafifilter mo na agad.

Example: sa pagtype ng name, dapat dun palang validated na kung may mga special characters na nilagay ang user bago mo ipasa ang values sa query mo

 

[d_ghost]

Re: Parameterizing an SQL query

SqlCommand cmd = new SqlCommand("INSERT INTO Profiles (name, age, gender, address, [contact no.]) VALUES (@fname + ' ' + @mName + ' ' + @sName, @age, @gender, @address, @contactNo)", conn);
cmd.Parameters.AddWithValue("@fname", txtfName.Text);
cmd.Parameters.AddWithValue("@mName", txtMName.Text);
cmd.Parameters.AddWithValue("@sName", txtSName.Text);
cmd.Parameters.AddWithValue("@age", cmbAge.Text);
cmd.Parameters.AddWithValue("@gender", cmbGender.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@contactNo", Convert.ToInt32(txtContactNum.Text));

To answer your question TS for possible enhancement. I think e concatenate mo nalang yong names using controls para reduce narin yong parameters at yung query string nya mabawasan ng operation to concat on the execution.

SqlCommand cmd = new SqlCommand("INSERT INTO Profiles (name, age, gender, address, [contact no.]) VALUES (@fullname, @age, @gender, @address, @contactNo)", conn);
cmd.Parameters.AddWithValue("@fullname", txtfName.Text + ' ' + txtMName.Text +' ' + txtSName.Text );
cmd.Parameters.AddWithValue("@age", cmbAge.Text);
cmd.Parameters.AddWithValue("@gender", cmbGender.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@contactNo", Convert.ToInt32(txtContactNum.Text));


Ok din yung suggestion nila ts na stored procedure though i would prefer parameterized query.

 

[TagaBicol]

Re: Parameterizing an SQL query

thanks guys, about dun sa Stored Procedure though i itried using SP i have some questions where do i have to create an SP for all my queries kahit na sa ibang queries ko mag reretrieve lng naman ako ng isang data or cell.

 

[blueclown25]

Re: Parameterizing an SQL query

thanks guys, about dun sa Stored Procedure though i itried using SP i have some questions where do i have to create an SP for all my queries kahit na sa ibang queries ko mag reretrieve lng naman ako ng isang data or cell.

Before in my previous work, we are using SP even select query lang. I asked why and researched about it and found out that SP is much faster than query. Tingin ko totoo naman in terms of big data. Kaso in my work now, hindi ko na nagawa yung ganun kasi gumawa ako ng mga helper class ko which is more on passing query lang tho pde ko naman gawin yung query na yun na SP pero tinamad na ko hehe. Gumawa ako ng helper para hindi ko na gagawin ng gagawin yung pag declare ng mga ado codes.

ex:
Inserting of Data - Query lang ang need (any query will do)
Retrieving of Data (Scalar) - Object ang return ko dito then explicit casting para mas flexible sa data types
Retrieving of Data (DataTable) - Query lang din ang params nito then yung return type ko is DataTable

yung helper class ko na yan library na po which is I'm using para hindi na ko paulit ulit gumawa

madami pa actually versatile naman sya kasi nagagamit ko sya sa ibat ibang projects ko

tingin ko TS may kanya kanya tayong diskarte in terms of programming, so nasa sayo naman kung gagamit ka ng SP or hindi.

isa pang reason TS kaya hindi na ko gumamit ng SP kasi more on entityframework na ginagamit ko ngayon. more on linq suddenly madugo kesa sa SQL (in my opinion) medyo hindi pa kasi tlaga ako ganun kagaling sa linq compare to SQL.

Good luck po!

 

[jfmjason]

Re: Parameterizing an SQL query

Before in my previous work, we are using SP even select query lang. I asked why and researched about it and found out that SP is much faster than query. Tingin ko totoo naman in terms of big data. Kaso in my work now, hindi ko na nagawa yung ganun kasi gumawa ako ng mga helper class ko which is more on passing query lang tho pde ko naman gawin yung query na yun na SP pero tinamad na ko hehe. Gumawa ako ng helper para hindi ko na gagawin ng gagawin yung pag declare ng mga ado codes.

ex:
Inserting of Data - Query lang ang need (any query will do)
Retrieving of Data (Scalar) - Object ang return ko dito then explicit casting para mas flexible sa data types
Retrieving of Data (DataTable) - Query lang din ang params nito then yung return type ko is DataTable

yung helper class ko na yan library na po which is I'm using para hindi na ko paulit ulit gumawa

madami pa actually versatile naman sya kasi nagagamit ko sya sa ibat ibang projects ko

tingin ko TS may kanya kanya tayong diskarte in terms of programming, so nasa sayo naman kung gagamit ka ng SP or hindi.

isa pang reason TS kaya hindi na ko gumamit ng SP kasi more on entityframework na ginagamit ko ngayon. more on linq suddenly madugo kesa sa SQL (in my opinion) medyo hindi pa kasi tlaga ako ganun kagaling sa linq compare to SQL.

Good luck po!

Tomo.... SP is for reporting lang


E MVC MO YAN Saglit lang yan hahaha