C# Code example using SqlConnection to connect to SQL Server

Connect to SQL Server 2012 using SqlConnection

Examples are as follows:

(1). Create a connection using SqlConnection

public SQLServerAPI(string str_ip, string str_db, string str_user, string str_pwd)
{
 m_strIp = str_ip;
 m_strDb = str_db;
 m_strUser = str_user;
 m_strPwd = str_pwd;
   
 //SQLServer authentication m_strConnection = @"Data Source=" + m_strIp;
 m_strConnection += @";Initial Catalog=" + m_strDb;
 m_strConnection += @";UID=" + m_strUser + ";PWD=" + m_strPwd;
 m_strConnection += ";Connection Timeout=10;Pooling=true;Max Pool Size=100";
 
 //Windows Authentication //m_strConnection = 
    @"server=localhost\SQLEXPRESS;database=SQL2012Db;Trusted_Connection=SSPI;";
   
 DisConnect();
 
 m_Transaction = null;
 m_SqlConnection = new SqlConnection(m_strConnection);
}

(2). Call the Open method to establish a session with the server.

/// &lt;summary&gt;
/// Try to connect to the database/// &lt;/summary&gt;
private bool Connect()
{
 if (m_SqlConnection == null)
  return false;
 
 try
 {
  m_SqlConnection.Open();
 }
 catch (Exception e)
 {
  ();
  return false;
 }
  return true;
}

(3). Call the Close() method to terminate the session

private bool DisConnect()
{
 if (m_SqlConnection == null)
  return true;
 
 try
 {
  m_SqlConnection.Close();
 }
 catch (Exception e)
 {
  ();
  return false;
 }
 return true;

Many programmers keep the connection open until the program ends, which usually wastes server resources. Compared to this way of opening once and never closing, using a connection pool makes it more efficient to open and close connections when needed.

As shown below, we encapsulate a function that executes SQL stored procedures:

/// &lt;summary&gt;
/// Execute stored procedure that returns query results/// &lt;/summary&gt;
/// <param name="procname">Stored procedure name?</param>/// <param name="param">parameter.  When the function returns normally, all parameter values of type out are also in the corresponding position</param>/// <param name="result">Return the query result</param>/// <returns>0 is correct, other errors</returns>public int ExecQueryStoreProc(string procname, ref SqlParameter[] param, out DataTable result)
{
	if (!Connect())
	{
		result = null;
		return -1;
	}
 
	try
	{
		SqlCommand command = new SqlCommand(procname, m_SqlConnection);
		 = ;
 
		if (m_Transaction != null)
			 = m_Transaction;
 
		SqlParameter rvalue = (new SqlParameter("RETURN_VALUE", ));
		 = ;
 
		if (param != null)
			(param);
 
		result = new DataTable();
		SqlDataReader reader = ();
		if ()
			(reader);
 
		return Convert.ToInt32(["RETURN_VALUE"].Value);
	}
	catch (Exception)
	{
		result = null;
		return -1;
	}
	finally
	{
		DisConnect();
	}
}

The above process is the implementation of opening and closing the connection when needed. In addition, the finally block always calls the Close() method, which does not cause problems or wastes too much resources, and can ensure that the connection is closed.

The above is a detailed explanation and integration of SQL Server creation connection code examples introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support for my website!