Learn to write a SQL Server Help class

The first two days of the lesson summary of the following, generally in the connection layer basically call those classes.

Generally includes three classes of System.Data.SqlClient and System.Data under Sqlconnection,sqlcommand,sqldatareader. If you want to read from the configuration file, you also need to system.configuration.

In the end how to connect I think there are books, I will not write. Write a single Sihuo, and the teacher in the class is not the same.

Method that's what everyone wrote at the beginning.

        Static stringConnStr =@"Data source=.\test;initial catalog=test;integrated security=true";  Public StaticSqlDataReader Getreader (stringSqlparamssqlparameter[] pars) {            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {                using(SqlCommand comm =NewSqlCommand (SQL, conn)) {Comm.                    Parameters.addrange (pars); Conn.                    Open (); returnComm.                ExecuteReader (commandbehavior.closeconnection); }            }        }

First get the connection string (here is the variable meaning), then pass sqlparameters array and SQL statement. Most people write the same thing. But defining the parameter array is too troublesome, there are many words, but also quite troublesome.

Later thought, there is no better way to do it. It would be nice to let him pick out the SQL variable himself. So I wrote the following method

        //remove SQL to get an array of variables, return an array of SQL variables of type list<string>        Private Staticlist<string> Getparamaters (stringSQL) {            stringMeat =SQL; Char[] CArray =", = () ><".            ToCharArray ();  for(inti =0; i < carray.length; i++) {Meat= Meat. Replace (Carray[i],'☆'); } List<string> list = meat. Split ('☆'). Where (s = s.contains ("@")).            ToList (); List=removerepeatpars (list); returnlist; }

The principle is that the SQL variables before and after the characters are so many, "= () <>", and then replaced with a rare character (such as the above small star), so as not to break the variable name. Then use String. Split

Throw them into a set by truncating them with useless characters so that we can break the variables before and after, and then take out the string containing the ' @ ' character, which are all clean variable names. Right.

But one more step is to remove the repetitive variables! (Sometimes when using update, set a variable where a variable may be repeated)

        Private Staticlist<string> Removerepeatpars (list<string>list) {             for(inti =0; I < list. Count; i++)            {                strings =List[i];  while(true)                {                    if(list. INDEXOF (s)! =list. LastIndexOf (s)) list. RemoveAt (list.                    LastIndexOf (s)); Else                         Break; }            }            returnlist; }

The index is not the same, probably is repeated. After the elimination of the pure SQL parameters left!

And then just change the original method.

        Private Staticlist<string> Getparamaters (stringSQL) {            stringMeat =SQL; Char[] CArray =", = () ><".            ToCharArray ();  for(inti =0; i < carray.length; i++) {Meat= Meat. Replace (Carray[i],'☆'); } List<string> list = meat. Split ('☆'). Where (s = s.contains ("@")).            ToList (); List=removerepeatpars (list); returnlist; }

Merge the two methods and the SQL processing is written. Let's change the previous method.

         Public StaticSqlDataReader Getreader (stringSqlparams Object[] pars) {List<string> list =getparamaters (SQL);//First cut into a parameter setif(list. Count! =Pars. Length) {Throw NewException ("The number of arguments in the statement does not match the given parameters! ");//Determine if the parameters in SQL are as many as the parameters given, in case the write is wrong}using(SqlConnection conn =NewSqlConnection (CONNSTR)) {                using(SqlCommand comm =NewSqlCommand (SQL, conn)) {                     for(inti =0; I < pars. Length; i++)//Anyway, the number of two arrays is as many as, choose a direct plus on the line {comm.                        Parameters.addwithvalue (List[i], pars[i]); Conn.                    Open (); }                    returnComm.                ExecuteReader (); }            }        }

Then use the params object[] array instead of the original sqlparameters parameters, so much more convenient, no longer have to define SqlParameters

If you want to write the parameters again, write them right.

Getreader (sql,1, "name", 0.14,1m)

Just fine. Just as convenient (self-feeling) as Console.WriteLine ().

Learn to write a SQL Server Help class