Shulou(Shulou.com)06/01 Report--

How c#.net accesses SQL Server database

1. Import namespaces

Using System.Data.SqlClient; / / dedicated to connect to SQLServer database

2. Create a connection

SqlConnection lo_conn = New SqlConnection ("Server= server name or IP;Database= database name; uid= user name; pwd= password")

3. Open the connection. Step 2 does not really connect to the database.

Lo_conn.Open (); / / actually connect to the database

4. SqlCommand is used to send SQL commands to the database:

SqlCommand lo_cmd = new SqlCommand (); / / create a command object

Lo_cmd.CommandText = "here is the SQL statement"; / / write the SQL statement

Lo_cmd.Connection = lo_con; / / specify the connection object, that is, the

5. Process SQL commands or return result sets

Lo_cmd.ExecuteNonQuery (); / / this only executes the SQL command and does not return the result set. It is useful for operations that do not need to return results, such as table creation, batch updates, and so on.

SqlDataReader lo_reader = lo_cmd.ExecuteReader (); / / returns the result set

6. return the result set in the form of a dataset

SqlDataAdapter dbAdapter = new SqlDataAdapter (lo_cmd); / / Note the distinction from the above

DataSet ds = new DataSet (); / / create a dataset object

DbAdapter.Fill (ds); / / populates the dataset with the returned result set, which can be manipulated by the control DataBind

7. Close the connection

Lo_conn.Close ()

General classes of C#.NET operating database (MS SQL Server)

The following is a general class for C # to operate on the MS SQL Server database, through which you can perform any operation on the database, including executing SQL statements and executing stored procedures. The following is the detailed implementation process. I hope you can modify and optimize it together. How to use it to implement N-tier programming will be described later.

Configure link parameters for web.config files

C # code

Using System

Using System.Data

Using System.Data.SqlClient

Namespace Com.LXJ.Database

{

/ / /

/ / A summary description of the ConnDB.

/ / /

Public class ConnDB

{

Protected SqlConnection Connection

Private string connectionString

/ / /

/ / default constructor

/ / /

Public ConnDB ()

{

String connStr

ConnStr = System.Configuration.ConfigurationSettings.AppSettings ["connStr"] .ToString ()

ConnectionString = connStr

Connection = new SqlConnection (connectionString)

}

/ / /

/ / Constructor with parameters

/ / /

/ / Database join string

Public ConnDB (string newConnectionString)

{

ConnectionString = newConnectionString

Connection = new SqlConnection (connectionString)

}

/ / /

/ / complete the instantiation of the SqlCommand object

/ / /

/ / /

/ / /

/ / /

Private SqlCommand BuildCommand (string storedProcName,IDataParameter [] parameters)

{

SqlCommand command = BuildQueryCommand (storedProcName,parameters)

Command.Parameters.Add (new SqlParameter ("ReturnValue", SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null))

Return command

}

/ / /

/ / create a new SQL command object (stored procedure)

/ / /

/ / /

/ / /

/ / /

Private SqlCommand BuildQueryCommand (string storedProcName,IDataParameter [] parameters)

{

SqlCommand command = new SqlCommand (storedProcName,Connection)

Command.CommandType = CommandType.StoredProcedure

Foreach (SqlParameter parameter in parameters)

{

Command.Parameters.Add (parameter)

}

Return command

}

/ / /

/ / execute the stored procedure with no return value

/ / /

/ / /

/ / /

Public void ExecuteProcedure (string storedProcName,IDataParameter [] parameters)

{

Connection.Open ()

SqlCommand command

Command=BuildQueryCommand (storedProcName,parameters)

Command.ExecuteNonQuery ()

Connection.Close ()

}

/ / /

/ / executes a stored procedure that returns the number of rows affected by the operation

/ / /

/ / /

/ / /

/ / /

/ / /

Public int RunProcedure (string storedProcName,IDataParameter [] parameters,out int rowsAffected)

{

Int result

Connection.Open ()

SqlCommand command = BuildCommand (storedProcName,parameters)

RowsAffected = command.ExecuteNonQuery ()

Result = (int) command.Parameters ["ReturnValue"] .Value

Connection.Close ()

C # connection to SQL database, common connection string explanation, common database operation methods

2009-06-15 12:45:47

Tag: database leisure workplace

Using System

Using System.Collections.Generic

Using System.Text

Using System.Data.SqlClient

Using System.Data

Namespace My_Connection

{

Class DbConn

{

Private const string ConnString = "server=localhost;integrated security=sspi;database=pubs;"

/ /-commonly used connection string parameters--

/ / server=locahost or. Login server address this is local

/ / Data Source

/ / Address

/ / Addr

/ / Network Address

/ / integrated security=sspi or true log in as Windows currently

/ / uid=sa; login user name: sa

/ / pwd=sa; login password: sa

/ / Connect Timeout=15 / / sets the connection wait time in seconds

/ / Trusted_Connection=True sets the trust connection

/ / Asynchronous Processing=true sets asynchronous access to the database. It is disabled by default.

/ / MultipleActiveResultSets=True gets and manages multiple forward-referenced and read-only result sets on a single connection (ADO.NET2.0,SQL 2005)

/ / database=pubs or Initial Catalog=pubs specify database: pubs

/ / maximum number of Max Pool Size connections

/ / minimum number of Min Pool Size connections

/ / Pooling when set to true, SQL connections will be obtained from connection pooling. If not, new connections will be created and added to connection pooling. If true.false is set by default, connection pooling will not be set.

/ / Connection Lifetime is applied in the connection pool, specifying that a connection whose lifetime after close is greater than the specified time and belongs to the minimum number of connections will be destroyed automatically.

/ / Application Name application name or .net SqlClient data provider when no application is provided

/ / Connection Reset true decides whether to reset the database connection when the connection is removed from the connection pool. Used to avoid the additional server round-trip cost of obtaining a connection when set to false

/ / when Enlist true is true, the connection pool automatically lists the connections in the current transaction context of the creation thread

/ / Workstation ID specifies the name of the workgroup

/ / Packet Size= is to set the size of network packets. Default is 8192.

/ / Network Library sets the network connection protocol

/ / use the following syntax when connecting through a SQLOLEDB provider:

/ / Network Library=dbmssocn

/ / the following syntax can be used when connecting through a MSDASQL provider:

/ / Network=dbmssocn

/ / name network protocol library

/ / dbnmpntw Win32 Named Pipes

/ / dbmssocn Win32 Winsock TCP/IP

/ / dbmsspxn Win32 SPX/IPX

/ / dbmsvinn Win32 Banyan Vines

/ / dbmsrpcn Win32 Multi-Protocol (Windows RPC)

/ /-example of a connection string

/ / to connect via IP address, make sure that the SQL server opens port 1433 and check the SQL network connection to enable the TCP/IP protocol.

/ / Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword

By default, the Microsoft .NET Framework data provider for the SQL server sets the network packet size to 8192 bytes.

/ / however, this is not necessarily the best. You can set the size of the bag according to the size you think is appropriate.

/ / Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096

/ /-

/ / because when implementing complex business logic, you need to use GetDataReader, GetDataAdapter, GetTransaction, etc.

/ / it will be troublesome to connect there, so distinguish and define the connection objects in three ways.

Private SqlConnection SqlDrConn = new SqlConnection (ConnString)

Private SqlConnection SqlDaConn = new SqlConnection (ConnString)

Private SqlConnection SqlTrConn = new SqlConnection (ConnString)

Public DataTable GetDataReader (string StrSql) / / data query

{

/ / close and then open when the connection is open, so that sometimes the data can not be updated in time.

If (SqlDrConn.State = = ConnectionState.Open)

{

SqlDrConn.Close ()

}

Try

{

SqlDrConn.Open ()

SqlCommand SqlCmd = new SqlCommand (StrSql, SqlDrConn)

SqlDataReader SqlDr = SqlCmd.ExecuteReader ()

If (SqlDr.HasRows)

{

DataTable dt = new DataTable ()

/ /-Method 1-

/ / for (int I = 0; I < SqlDr.FieldCount; iTunes +)

/ / {

/ / dt.Columns.Add (SqlDr.GetName (I), SqlDr.GetFieldType (I))

/ /}

/ / while (SqlDr.Read ())

/ / {

/ / DataRow dr= dt.NewRow ()

/ / for (int I = 0; I < SqlDr.FieldCount; iTunes +)

/ / {

/ / dr [I] = SqlDr [I]

/ /}

/ / dt.Rows.Add (dr)

/ /}

/ /-Method 2-

/ / more simple

Dt.Load (SqlDr)

/ / close objects and connections

SqlDr.Close ()

SqlDrConn.Close ()

Return dt

}

Return null

}

Catch (Exception ex)

{

System.Windows.Forms.MessageBox.Show (ex.Message)

Return null

}

Finally

{

SqlDrConn.Close ()

}

}

Public SqlDataAdapter GetDataAdapter (string StrSql) / / data addition, deletion, revision and query

{

If (SqlDaConn.State = = ConnectionState.Open)

{

SqlDaConn.Close ()

}

Try

{

SqlDaConn.Open ()

SqlDataAdapter SqlDa = new SqlDataAdapter (StrSql, SqlDaConn)

/ / provide a way to automatically generate single table commands

SqlCommandBuilder SqlCb = new SqlCommandBuilder (SqlDa)

Return SqlDa

}

Catch (Exception ex)

{

System.Windows.Forms.MessageBox.Show (ex.Message)

Return null

}

Finally

{

SqlDaConn.Close ()

}

}

/ / the ArrayList array is used to store the set of SQL statements.

Public bool GetTransaction (System.Collections.ArrayList StrSqlList) / / implement transaction

{

If (SqlTrConn.State = = ConnectionState.Open)

{

SqlTrConn.Close ()

}

SqlTransaction SqlTr = null

Try

{

SqlTrConn.Open ()

SqlTr = SqlTrConn.BeginTransaction ()

SqlCommand SqlCmd = new SqlCommand ()

SqlCmd.Connection = SqlTrConn

SqlCmd.Transaction = SqlTr

/ / load the SQL statement in the array

For (int I = 0; I < StrSqlList.Count; iTunes +)

{

SqlCmd.CommandText = StrSqlList [I] .ToString ()

SqlCmd.ExecuteNonQuery ()

}

/ / commit all transactions

SqlTr.Commit ()

Return true

}

Catch (Exception ex)

{

/ / once there is an error, roll back all transactions and restore the status

If (SqlTrackers null) SqlTr.Rollback ()

System.Windows.Forms.MessageBox.Show (ex.Message)

Return false

}

Finally

{

SqlTrConn.Close ()

}

}

}

}

Encapsulation of common database operations (connect, add, delete, modify) in C#.NET

If you are often engaged in database development for .NET-based applications, you will have the feeling that you are writing the same data access code over and over again. A lot of similar code is copied and pasted every day. Have you ever thought of wrapping data access code in a Helper function so that it can be shared among different classes? If you haven't already done so, I'll show you how to design and package our own data access class from a reuse perspective to reduce redundant code and improve development efficiency. (code location of this section: CD\ code\ ch05\ 02)

When we execute add, update, or delete commands, we usually write like this:

1. String conString = "data source=127.0.0.1;database=codematic;user

Id=sa

2. Password= "

3. SqlConnection myConnection = new SqlConnection (conString)

4. String strSql = "update P_Product set Name=' computer 3' where Id=52"

5. SqlCommand myCommand = new SqlCommand (strSql, myConnection)

6. MyConnection.Open ()

7. Int rows = myCommand.ExecuteNonQuery ()

8. MyConnection.Close ()

If there are a lot of such operations, then we need to write a lot of basically similar code. According to the principle of abstraction facing objects, there are many things in common in these operations, the only difference is conString and strSql. Can I abstract something in common and encapsulate it for all operations?

We abstract things in common into a separate method:

1. / /

2. / / execute the SQL statement to return the number of records affected

3. / /

4. Public int ExecuteSql (string StrSql, string conString)

5. {

6. Using (SqlConnection connection = new SqlConnection (conString)

7. {

8. Using (SqlCommand cmd = new SqlCommand (StrSql, connection))

9. {

10. Connection.Open ()

11. Int rows = cmd.ExecuteNonQuery ()

12. Return rows

13.}

14.}

15.}

In this way, the actual calling code can become:

1. String conString= "data source=127.0.0.1;database=codematic;user

Id=sa

2. Password= "

3. String strSql = "update P_Product set Name=' computer 3' where Id=52"

4. Int rows = ExecuteSql (strSql, conString)

Isn't it a lot easier? This method can be shared by other classes, resulting in less repetitive code and higher productivity.

If the database is fixed, that is, the connection string is generally the same, then we can extract the conString into a variable, which is used in all methods. When you want to change the database, you can modify the contents of the variable directly, and you don't need to pass the connection string when you actually call it. Is that another step saved? For better reuse, we can also put this method in a separate class DbHelperSQL.

In the DbHelperSQL class:

1. Public class DbHelperSQL

2. {

3. Public static string conString = "data source=127.0.0.1

4. Database=codematic;user id=sa;password= "

5. / /

6. / / execute the SQL statement to return the number of records affected

7. / /

8. / / SQL statement

9. / / number of records affected

10. Public static int ExecuteSql (string StrSql)

11. {

Using (SqlConnection connection = new SqlConnection (conString))

13. {

14. Using (SqlCommand cmd = new SqlCommand (StrSql, connection))

15. {

16. Connection.Open ()

17. Int rows = cmd.ExecuteNonQuery ()

18. Return rows

19.}

20.}

21. }

22.}

In order to make it easy to use and save resources, we make variables and methods static. The call at this point becomes:

1. String strSql = "update P_Product set Name=' computer 3' where Id=52"

2. Int rows = DbHelperSQL.ExecuteSql (strSql)

For future maintenance and expansion, the database connection string had better be placed in Web.config, so that if you want to change the database in the future, you can directly change the Web.config without recompiling the code.

1. Join Web.config:

two。

3.

5.

6. Variable to get the string:

7. Public static string conString = ConfigurationManager.AppSettings

8. ["ConnectionString"]

Sometimes we may encrypt the connection string in Web.config for security reasons. This requires decryption when getting the connection string. In this way, the above line of code may not be solved, and we need a separate processing method to deal with it. To reduce coupling and dependence on classes, we can put the operation of getting the database connection string into a separate class, such as PubConstant. In this way, when you modify the corresponding connection rules and encryption processing in the future, you can just call this class of each module directly, and you don't need to know how the actual database access class of each module is obtained.

For example, the settings of Web.config:

1.

two。

4. The common constant class gets and processes:

5. Public class PubConstant

6. {

7. / /

8. / / get the connection string to determine whether it is encrypted or not

9. / /

10. Public static string ConString

11. {

12. Get

13. {

14. String _ conString = ConfigurationManager.AppSettings

["ConString"]

15. String ConStringEncrypt = ConfigurationManager.AppSettings

16. ["ConStringEncrypt"]

17. If (ConStringEncrypt = = "true") / / is encrypted

18. {

19. _ conString = DESEncrypt.Decrypt (_ conString); / / decrypt

20.}

21. Return _ conString

twenty-two。 }

23.}

24.}

25. Use the connection string:

26.public static string conString = PubConstant.ConString

Common ways for C # to operate databases!

Category: Winform project experience 2012-09-03 18:46 285 people read comments (0) Collection report

Even a small client + database project (this article uses SQL SERVER as an example, other databases are similar) requires a variety of operations on the database. Sometimes when coding, it is true that where to write can achieve specific functions, but from the perspective of the entire framework, the redundancy and non-standardization of the code is likely to lead to the efficiency of the code running. it also has disadvantages rather than advantages to expand the scale of the project in the later stage. I have made many such mistakes in the past, and I am determined not to make them again.

All right, let's get down to business. Often operating the database is nothing more than connecting to the database, executing SQL statements, and displaying the results of three major steps. In general, these three steps can be carried out in two ways: one is to create a SqlDataAdapter to populate the dataset DataSet;, and the other is to use the Commd object to execute the SQL statement, and then establish the SqlDataReader. The former is mainly used to bind display controls, while the latter tends to judge certain conditions. In short, the general operation process is like this, when we operate the database many times, there is bound to be duplicate code, so it is necessary to establish a common method.

Create a public class, as follows

[csharp] view plaincopyprint?

Using System

Using System.Collections.Generic

Using System.Linq

Using System.Text

Using System.Data.SqlClient

Using System.Data

Namespace BaseClass

{

Class BaseOperate

{

/ / Connect to the database

Public SqlConnection getcon ()

{

String M_str_sqlcon = "Server=.;DataBase=db_CRM;Trusted_Connection=SSPI"

SqlConnection myCon = new SqlConnection (M_str_sqlcon)

Return myCon

}

/ / Connect to SqlConnection and execute SQL

Public void getcom (string M_str_sqlstr)

{

SqlConnection sqlcon = this.getcon ()

Sqlcon.Open ()

SqlCommand sqlcom = new SqlCommand (M_str_sqlstr, sqlcon)

Sqlcom.ExecuteNonQuery ()

Sqlcom.Dispose ()

Sqlcon.Close ()

Sqlcon.Dispose ()

}

/ / create a DataSet object

Public DataSet getds (string M_str_sqlstr, string M_str_table)

{

SqlConnection sqlcon = this.getcon ()

SqlDataAdapter sqlda = new SqlDataAdapter (M_str_sqlstr, sqlcon)

DataSet myds = new DataSet ()

Sqlda.Fill (myds, M_str_table)

Return myds

}

/ / create a SqlDataReader object

Public SqlDataReader getread (string M_str_sqlstr)

{

SqlConnection sqlcon = this.getcon ()

SqlCommand sqlcom = new SqlCommand (M_str_sqlstr, sqlcon)

Sqlcon.Open ()

SqlDataReader sqlread = sqlcom.ExecuteReader (CommandBehavior.CloseConnection)

Return sqlread

}

}

}

Write each step as a public method as above, and you can call it in each module at will, which not only makes the code simple and clear, but also helps others to read it. In addition, the naming of variables also needs to have its own naming rules, otherwise it will be confused when the variables are very common.

With the above groundwork, you can continue to consider the public methods that may be used according to the above ideas, among which the data binding of controls (such as drop-down boxes) and the determination of regular expressions are the most commonly used. Immediately write another public class and add the following public methods:

[csharp] view plaincopyprint?

Using System

Using System.Collections.Generic

Using System.Linq

Using System.Text

Using System.Data.SqlClient

Using System.Data

Using System.Windows.Forms

Using System.Text.RegularExpressions

Namespace BaseClass

{

Class OperateAndValidate

{

BaseOperate boperate = new BaseOperate (); / / declare an object of the BaseOperate class to call its method

/ / bind COMBOBOX control

Public void cboxBind (string P_str_sqlstr, string P_str_table, string P_str_tbMember, ComboBox cbox)

{

DataSet myds = boperate.getds (P_str_sqlstr, P_str_table)

Cbox.DataSource = myds.tables [P _ str_table]; / / bind tables in the dataset to the drop-down box

Cbox.DisplayMember = displaying the values of the specific columns in the table in the drop-down box

}

/ / verify that the input string is a number

Public bool validateNum (string P_str_num)

{

Return Regex.IsMatch (P_str_num, "^ [0-9] * $")

}

/ / verify that the input string is a phone number

Public bool validatePhone (string P_str_phone)

{

Return Regex.IsMatch (P_str_phone, @ "\ d {3pr 4} -\ d {7pr 8}")

}

/ / verify that the input string is a fax number

Public bool validateFax (string P_str_fax)

{

Return Regex.IsMatch (P_str_fax, @ "86 -\ d {2pr 3} -\ d {7pr 8}")

}

/ / verify that the input string is a zip code number

Public bool validatePostCode (string P_str_postcode)

{

Return Regex.IsMatch (P_str_postcode, @ "\ d {6}")

}

# endregion

/ / verify that the input string is an E-MAIL address

Public bool validateEmail (string P_str_email)

{

Return Regex.IsMatch (P_str_email, @ "\ w + ([- +.']\ w +) * @\ w + ([-.]\ w +) *.\ w + ([-.]\ w +) *")

}

/ / verify that the input string is a network address

Public bool validateNAddress (string P_str_naddress)

{

Return Regex.IsMatch (P_str_naddress, @ "http (s)?: / / ([\ w -] +\.) + [\ w -] + (/ [\ w -. /?% & =] *?)

}

/ / automatic numbering

/ / /

/ / automatic numbering

/ / /

/ SQL statement

/ / data sheet

/ / datasheet field

/ / string before numbering

/ / numbered number

/ textBox control name

Public void autoNum (string P_str_sqlstr, string P_str_table, string P_str_tbColumn, string P_str_codeIndex, string P_str_codeNum, TextBox txt)

{

String P_str_Code = ""

Int P_int_Code = 0

DataSet myds = boperate.getds (P_str_sqlstr, P_str_table)

If (myds.Tables [0] .Rows.Count = = 0)

{

Txt.Text = P_str_codeIndex + P_str_codeNum

}

Else

{

P_str_Code = Convert.ToString (myds.Tables [0] .Rows [myds.Tables [0] .Rows.Count-1] [P_str_tbColumn]); / / get the numbered field in the last row of data

P_int_Code = Convert.ToInt32 (P_str_Code.Substring (1,3)) + 1 * / my field is "C101Magi C102."

P_str_Code = P_str_codeIndex + P_int_Code.ToString ()

Txt.Text = P_str_Code

}

}

}

}

In the public method of automatic numbering, the format of extracting strings is different, because you don't know any way to achieve self-increment, you can only use C101 for self-increment (if set to C001, according to the above method, the second one is C2). This method will work.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.