cyf
256 lines
8.6 KiB
C#
256 lines
8.6 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Runtime.InteropServices;
|
|
|
|
public abstract class SqlHelper
|
|
{
|
|
public static SqlConnection conn;
|
|
|
|
protected SqlHelper()
|
|
{
|
|
}
|
|
|
|
public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, string tabName, params SqlParameter[] commandParameters)
|
|
{
|
|
DataSet set;
|
|
SqlCommand cmd = new SqlCommand();
|
|
try
|
|
{
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet dataSet = new DataSet();
|
|
adapter.Fill(dataSet, tabName);
|
|
set = dataSet;
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
return set;
|
|
}
|
|
|
|
public static DataSet ExecuteDataSet(int cmdType, string cmdText, string tabName, params SqlParameter[] commandParameters)
|
|
{
|
|
CommandType text = CommandType.Text;
|
|
switch (cmdType)
|
|
{
|
|
case 4:
|
|
text = CommandType.StoredProcedure;
|
|
break;
|
|
|
|
case 512:
|
|
text = CommandType.TableDirect;
|
|
break;
|
|
}
|
|
return ExecuteDataSet(text, cmdText, tabName, commandParameters);
|
|
}
|
|
|
|
public static DataSet ExecuteDataSet(string procedureName, string tabName, string[] parameterNames, object[] parameterValues)
|
|
{
|
|
DataSet set;
|
|
try
|
|
{
|
|
SqlCommand selectCommand = new SqlCommand(procedureName, conn)
|
|
{
|
|
CommandType = CommandType.StoredProcedure
|
|
};
|
|
if ((parameterNames != null) && (parameterValues != null))
|
|
{
|
|
for (int i = 0; (i < parameterNames.Length) && (i < parameterValues.Length); i++)
|
|
{
|
|
selectCommand.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
|
|
}
|
|
}
|
|
SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
|
|
DataSet dataSet = new DataSet();
|
|
adapter.Fill(dataSet, tabName);
|
|
set = dataSet;
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
return set;
|
|
}
|
|
|
|
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
int num = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return num;
|
|
}
|
|
|
|
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
int num = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return num;
|
|
}
|
|
|
|
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
|
|
int num = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return num;
|
|
}
|
|
|
|
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlDataReader reader;
|
|
SqlCommand cmd = new SqlCommand();
|
|
try
|
|
{
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
SqlDataReader reader2 = cmd.ExecuteReader();
|
|
cmd.Parameters.Clear();
|
|
reader = reader2;
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
return reader;
|
|
}
|
|
|
|
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
object obj2 = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return obj2;
|
|
}
|
|
|
|
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
object obj2 = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return obj2;
|
|
}
|
|
|
|
public static DataTable PageDataQuery(int ToPage, int PageSize, string Sele_mx, string id, string Froms, out int AllRecords)
|
|
{
|
|
return PageDataQuery(ToPage, PageSize, "", "", Sele_mx, id, Froms, out AllRecords);
|
|
}
|
|
|
|
public static DataTable PageDataQuery(int ToPage, int PageSize, string Order_by, string Sele_mx, string id, string Froms, out int AllRecords)
|
|
{
|
|
return PageDataQuery(ToPage, PageSize, "", Order_by, Sele_mx, id, Froms, out AllRecords);
|
|
}
|
|
|
|
public static DataTable PageDataQuery(int ToPage, int PageSize, string WhereString, string Order_by, string Sele_mx, string id, string Froms, out int AllRecords)
|
|
{
|
|
DataTable table = new DataTable();
|
|
SqlCommand command = new SqlCommand("Common_FenYe", conn)
|
|
{
|
|
CommandType = CommandType.StoredProcedure
|
|
};
|
|
command.Parameters.Add("@ToPage", SqlDbType.Int).Value = ToPage;
|
|
command.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
|
|
command.Parameters.Add("@WhereString", SqlDbType.VarChar, 4000).Value = WhereString;
|
|
command.Parameters.Add("@Order_by", SqlDbType.VarChar, 500).Value = Order_by;
|
|
command.Parameters.Add("@Sele_mx", SqlDbType.VarChar, 1000).Value = Sele_mx;
|
|
command.Parameters.Add("@id", SqlDbType.VarChar, 30).Value = id;
|
|
command.Parameters.Add("@Froms", SqlDbType.VarChar, 2000).Value = Froms;
|
|
command.Parameters.Add("@AllRecords", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
|
|
try
|
|
{
|
|
SqlDataReader reader = command.ExecuteReader();
|
|
table.Load(reader);
|
|
reader.Close();
|
|
AllRecords = int.Parse(command.Parameters["@AllRecords"].Value.ToString());
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
command.Dispose();
|
|
}
|
|
return table;
|
|
}
|
|
|
|
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
|
|
{
|
|
if (conn.State != ConnectionState.Open)
|
|
{
|
|
conn.Open();
|
|
}
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = cmdText;
|
|
if (trans != null)
|
|
{
|
|
cmd.Transaction = trans;
|
|
}
|
|
cmd.CommandType = cmdType;
|
|
if (cmdParms != null)
|
|
{
|
|
foreach (SqlParameter parameter in cmdParms)
|
|
{
|
|
cmd.Parameters.Add(parameter);
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// <para>说明：获取C#连接字符串</para>
|
|
/// <para>创建人：龚宇超</para>
|
|
/// <para>创建日期：2017-08-07 </para>
|
|
/// <para>修改人：</para>
|
|
/// <para>修改日期：</para>
|
|
/// <para>修改备注：</para>
|
|
/// <para>版本：1.0</para>
|
|
/// </summary>
|
|
/// <returns>System.String.</returns>
|
|
public static string GetConnection(string ServerName, string DataBase)
|
|
{
|
|
return string.Format("Server={0};Database={1};Persist Security Info=True;User ID=lserpAdmin;Password=lserp110;Connection Timeout=5", ServerName, DataBase);
|
|
}
|
|
/// <summary>
|
|
/// <para>说明：创建数据库连接</para>
|
|
/// <para>创建人：龚宇超</para>
|
|
/// <para>创建日期：2017-08-07 </para>
|
|
/// <para>修改人：</para>
|
|
/// <para>修改日期：</para>
|
|
/// <para>修改备注：</para>
|
|
/// <para>版本：1.0</para>
|
|
/// </summary>
|
|
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
|
|
public static bool CreateConnection(string servername, string dataBase)
|
|
{
|
|
string sqlconStr = GetConnection(servername, dataBase);
|
|
if (SqlHelper.conn != null)
|
|
{
|
|
try
|
|
{
|
|
SqlHelper.conn.Close();
|
|
SqlHelper.conn.Dispose();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
}
|
|
SqlHelper.conn = null;
|
|
}
|
|
try
|
|
{
|
|
SqlHelper.conn = new SqlConnection(sqlconStr);
|
|
SqlHelper.conn.Open();
|
|
return true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
|