TXT

SQLHelper

By Leo Bryant,2014-10-30 15:56
11 views 0
SQLHelper

    //Êý?Ý?âÉè?ƺÃÁË,ÎÒÃÇ?ªÊ?Éè?ÆSQLHelperÁË,ÊÇÒ??öSQL?ùÀà.

     //Á??ÓÊý?ÝÔ?:

private SqlConnection myConnection = null;

    private readonly string RETURNVALUE = "RETURNVALUE"; //?ò?ªÊý?Ý?âÁ??Ó.

private void Open()

     {

     // ?ò?ªÊý?Ý?âÁ??Ó

     if (myConnection == null)

     {

     // myConnection = new

    SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

     myConnection = new

    SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());

     }

     if (myConnection.State == ConnectionState.Closed)

     {

     try

     {

     ///?ò?ªÊý?Ý?âÁ??Ó

     myConnection.Open();

     }

     catch (Exception ex)

     {

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÒÑ?-?ò?ªµÄÊý?Ý?âÁ??Ó

     }

     }

     }

    //?Ø?ÕÊý?Ý?âÁ??Ó

public void Close()

     {

     ///ÅÐ?ÏÁ??ÓÊÇ?ñÒÑ?-????

     if (myConnection != null)

     {

     ///ÅÐ?ÏÁ??ÓµÄ×?Ì?ÊÇ?ñ?ò?ª

     if (myConnection.State == ConnectionState.Open)

     {

     myConnection.Close();

     }

     }

     }

    //ÊÍ?Å×ÊÔ?

public void Dispose()

{

     // È?ÈÏÁ??ÓÊÇ?ñÒÑ?-?Ø?Õ

     if (myConnection != null)

     {

     myConnection.Dispose();

     myConnection = null;

     }

}

    //Ö?ÐÐÎÞ?ÎÊýºÍ?µ?ØintÐ͵Ä?æ???ý?Ì

    public int RunProc(string procName)

     {

     SqlCommand cmd = CreateProcCommand(procName, null);

     try

     {

     ///Ö?ÐÐ?æ???ý?Ì

     cmd.ExecuteNonQuery();

     }

     catch (Exception ex)

     {

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÊý?Ý?âµÄÁ??Ó

     Close();

     }

     ///?µ?Ø?æ???ý?̵Ä?ÎÊýÖµ

     return (int)cmd.Parameters[RETURNVALUE].Value;

     }

    //Ö?ÐÐ??Èë?ÎÊýºÍ?µ?ØintÐ͵Ä?æ???ý?Ì

    public int RunProc(string procName, SqlParameter[] prams)

     {

     SqlCommand cmd = CreateProcCommand(procName, prams);

     try

     {

     ///Ö?ÐÐ?æ???ý?Ì

     cmd.ExecuteNonQuery();

     }

     catch (Exception ex)

     {

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÊý?Ý?âµÄÁ??Ó

     Close();

     }

     ///?µ?Ø?æ???ý?̵Ä?ÎÊýÖµ

     return (int)cmd.Parameters[RETURNVALUE].Value;

     }

     //Ö?ÐÐ?æ???ý?̺Í?µ?ØSqlDataReader

    public void RunProc(string procName, out SqlDataReader dataReader)

{

     ///????Command

     SqlCommand cmd = CreateProcCommand(procName, null);

     try

     {

     ///?ÁÈ?Êý?Ý

     dataReader =

    cmd.ExecuteReader(CommandBehavior.CloseConnection);

     }

     catch (Exception ex)

     {

     dataReader = null;

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

}

     //Ö?ÐÐ??Èë?ÎÊýºÍ?µ?ØSqlDataReader?æ???ý?Ì

public void RunProc(string procName, SqlParameter[] prams, out

    SqlDataReader dataReader)

     {

     ///????Command

     SqlCommand cmd = CreateProcCommand(procName, prams);

     try

     {

     ///?ÁÈ?Êý?Ý

     dataReader =

    cmd.ExecuteReader(CommandBehavior.CloseConnection);

     }

     catch (Exception ex)

     {

     dataReader = null;

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     }

    //Ö?ÐÐÎÞ?ÎÊý?æ???ý?Ì?µ?ØDataSet

public void RunProc(string procName, ref DataSet dataSet)

{

     if (dataSet == null)

     {

     dataSet = new DataSet();

     }

     ///????SqlDataAdapter

     SqlDataAdapter da = CreateProcDataAdapter(procName, null);

     try

     {

     ///?ÁÈ?Êý?Ý

     da.Fill(dataSet);

     }

     catch (Exception ex)

     {

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÊý?Ý?âµÄÁ??Ó

     Close();

     }

}

    //Ö?ÐÐ??Èë?ÎÊýµÄ?æ???ý?Ì?µ?ØDataSet

public void RunProc(string procName, SqlParameter[] prams, ref DataSet

    dataSet)

     {

     if (dataSet == null)

     {

     dataSet = new DataSet();

     }

     ///????SqlDataAdapter

     SqlDataAdapter da = CreateProcDataAdapter(procName, prams);

     try

     {

     ///?ÁÈ?Êý?Ý

     da.Fill(dataSet);

     }

     catch (Exception ex)

     {

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÊý?Ý?âµÄÁ??Ó

     Close();

     }

     }

    //Ö?ÐÐ??Èë?ÎÊýºÍ?íÃûµÄ?æ???ý?Ì?µ?ØDataSet

public void RunProc(string procName, SqlParameter[] prams, string

    TableName, ref DataSet dataSet)

     {

     if (dataSet == null)

     {

     dataSet = new DataSet();

     }

     ///????SqlDataAdapter

     SqlDataAdapter da = CreateProcDataAdapter(procName,

    prams);

     try

     {

     ///?ÁÈ?Êý?Ý

     da.Fill(dataSet, TableName);

     }

     catch (Exception ex)

     {

     ///?ÇÂ??íÎóÈÕÖ?

     SystemError.CreateErrorLog(ex.Message);

     }

     finally

     {

     ///?Ø?ÕÊý?Ý?âµÄÁ??Ó

     Close();

     }

     }

    //Ö?ÐÐÎÞ?ÎÊýSQLÓï?ä

    public int RunSQL(string cmdText)

{

     SqlCommand cmd = CreateSQLCommand(cmdText, null);

     try

     {

Report this document

For any questions or suggestions please email
cust-service@docsford.com