C # using SQL

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Configuration;
Using System. Data;
Using System. Data. SqlClient;
Using System. Collections;


Using System. IO;
Using System. Text;
Using System. Data. SqlClient;
Using System. Xml;

/// <Summary>
/// Summary description of SQLHelper
/// </Summary>
Public class SQLHelper
{

Private static readonly string ConnectionString = ConfigurationManager. ConnectionStrings ["LocalSqlServer"]. ConnectionString;



# Region ExecuteReader (): returns the number of rows and returns a data set.


/// <Summary>
/// Parse a SqlCommand line and return a result set
/// </Summary>
/// <Param name = "strSql"> SQL statement </param>
/// <Param name = "Parms"> metric data </param>
/// <Returns> </returns>
Public SqlDataReader ExecuteReader (string strSql, SqlParameter [] Parms)
{
Try
{
Using (SqlConnection conn = new SqlConnection (ConnectionString ))
{
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}

Using (SqlCommand cmd = new SqlCommand ())
{
Cmd. Connection = conn;
Cmd. CommandText = strSql;
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. AddRange (Parms );
Conn. Open ();
SqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Return rdr;
Conn. Close ();
}
}

}
Catch (Exception ex)
{
// Fn_share = new fn_share ();
SystemErrorLog (strSql + ex. Message );

Return (SqlDataReader) null;
}


}
# Endregion


# Region ExecuteNonQuery (): returns a result in the response line. 1 indicates success, and-1 indicates failure.

/// <Summary>
/// Returns a result after the row is deleted. 1 indicates success, and-1 indicates failure.
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Param name = "Parms"> </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string strSql, SqlParameter [] Parms)
{
Try
{
Int retcount =-1;
Using (SqlConnection conn = new SqlConnection (ConnectionString ))
{
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}

Using (SqlCommand cmd = new SqlCommand ())
{
Cmd. Connection = conn;
Cmd. CommandText = strSql;
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. AddRange (Parms );
Conn. Open ();
Retcount = cmd. ExecuteNonQuery ();
Conn. Close ();
Cmd. Parameters. Clear ();

}
}

Return retcount;
}
Catch (Exception ex)
{
SystemErrorLog (strSql + ex. Message );

Return-1;
}

}
# Endregion

# Region ExecuteScalar (): returns the number of the first column in the first row of the dataset.

/// <Summary>
/// Return the data in the first column of the first row of the dataset.
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Param name = "Parms"> metric data </param>
/// <Returns> </returns>
Public object ExecuteScalar (string strSql, SqlParameter [] Parms)
{
Object retobject = null;
Try
{
Using (SqlConnection conn = new SqlConnection (ConnectionString ))
{
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}

Using (SqlCommand cmd = new SqlCommand ())
{
Cmd. Connection = conn;
Cmd. CommandText = strSql;
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. AddRange (Parms );
Conn. Open ();
Retobject = cmd. ExecuteScalar ();
Conn. Close ();
Cmd. Parameters. Clear ();

}
}
}
Catch (Exception ex)
{
SystemErrorLog (strSql + ex. Message );



}


Return retobject;
}
# Endregion

# Region GetDataTable (): the root response SQL returns a data table.

/// <Summary>
/// The root response SQL statement returns a data table
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Param name = "Parms"> </param>
/// <Returns> </returns>
Public DataTable GetDataTable (string strSql, SqlParameter [] Parms)
{
Try
{
DataTable dt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (ConnectionString ))
{
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}

Using (SqlCommand cmd = new SqlCommand ())
{
Cmd. Connection = conn;
Cmd. CommandText = strSql;
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. AddRange (Parms );
Conn. Open ();
SqlDataReader dr = cmd. ExecuteReader ();
Dt. Load (dr );
Conn. Close ();

Cmd. Parameters. Clear ();

}
}
Return dt;

}
Catch (Exception ex)
{
SystemErrorLog (strSql + ex. Message );

Return (DataTable) null;
}

}
# Endregion

# Region GetDataSet (): the root response SQL returns a data set.

/// <Summary>
/// The root response SQL statement returns a data set.
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Param name = "Parms"> </param>
/// <Returns> </returns>
Public DataSet GetDataSet (string strSql, SqlParameter [] Parms)
{

Try
{
DataSet ds = new DataSet ();
Using (SqlConnection conn = new SqlConnection (ConnectionString ))
{
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}
Using (SqlCommand cmd = new SqlCommand ())
{
Cmd. Connection = conn;
Cmd. CommandText = strSql;
Cmd. CommandTimeout = 0;
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. AddRange (Parms );

SqlDataAdapter da = new SqlDataAdapter (cmd );

Da. Fill (ds );
Conn. Close ();
Cmd. Parameters. Clear ();
}
}
Return ds;
}
Catch (Exception ex)
{
SystemErrorLog (strSql + ex. Message );


Return (DataSet) null;
}

}
# Endregion

Public void systemErrorLog (string msg)
{
// Sqlconn_Close ();
String filename = ConfigurationManager. deleetask[ "UploadFilePath"]. toString () + @ "\ file \ log \ weblog _" + DateTime. today. toString ("yyyyMMdd") + ". txt ";

If (File. Exists (filename. Trim ()))
{
StreamWriter sw = File. AppendText (filename. Trim ());
Sw. WriteLine ("\ n ");
Sw. WriteLine (DateTime. Now. ToString () + msg );
Sw. Close ();
}
Else
{
StreamWriter sw = new StreamWriter (filename, false, Encoding. UTF8 );
Sw. Write (msg );
Sw. Close ();

}


}

}

 

 public DataSet UserCart_List(string flag, string Guid, string UserCode, string DirectorCode, string SendNum, string paravalue)    {        SqlParameter[] param = new SqlParameter[]         {                        new SqlParameter("@flag",flag),                        new SqlParameter("@Guid",Guid),                        new SqlParameter("@UserCode",UserCode),                        new SqlParameter("@DirectorCode",DirectorCode),                        new SqlParameter("@SendNum",SendNum),                        new SqlParameter("@paravalue",paravalue)         };        DataTable dt = new DataTable();        return SQLHelper.GetDataSet("usp_BCC_Send_UserCart_List", param);        //try        //{        //    sqlcomm("usp_BCC_Send_UserCart_List");        //    mycomm.Parameters.AddWithValue("@flag", flag.Trim());        //    mycomm.Parameters.AddWithValue("@Guid", Guid.Trim());        //    mycomm.Parameters.AddWithValue("@UserCode", UserCode.Trim());        //    mycomm.Parameters.AddWithValue("@DirectorCode", DirectorCode.Trim());        //    mycomm.Parameters.AddWithValue("@SendNum", SendNum.Trim());        //    mycomm.Parameters.AddWithValue("@paravalue", paravalue.Trim());        //    SqlDataAdapter da = new SqlDataAdapter(mycomm);        //    DataSet ds = new DataSet();        //    da.Fill(ds, "DeptAssistant_List");        //    sqlconn_Close();        //    return ds;        //}        //catch (Exception ee)        //{        //    systemErrorLog("UserCart_List" + ee.Message);        //    sqlconn_Close();        //    return (DataSet)null;        //}    }