619 lines
22 KiB
C#
619 lines
22 KiB
C#
|
using System;
|
|||
|
|
using System.Collections;
|
|||
|
|
using System.Collections.Specialized;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using System.Configuration;
|
|||
|
|
using System.Data.Common;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Web.UI.WebControls;
|
|||
|
|
using System.Security.Cryptography;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Web.Configuration;
|
|||
|
|
|
|||
|
|
namespace ZWL.DBUtility
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20><><EFBFBD>ݷ<EFBFBD><DDB7>ʳ<EFBFBD><CAB3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
/// Copyright (C) 2004-2008 By zwl
|
|||
|
|
/// </summary>
|
|||
|
|
public abstract class DbHelperSQL
|
|||
|
|
{
|
|||
|
|
public DbHelperSQL()
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
//<2F>Լ<EFBFBD><D4BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD><DDBF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD>
|
|||
|
|
protected static string DecryptDBStr(string Text, string sKey)
|
|||
|
|
{
|
|||
|
|
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
|
|||
|
|
int len;
|
|||
|
|
len = Text.Length / 2;
|
|||
|
|
byte[] inputByteArray = new byte[len];
|
|||
|
|
int x, i;
|
|||
|
|
for (x = 0; x < len; x++)
|
|||
|
|
{
|
|||
|
|
i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
|
|||
|
|
inputByteArray[x] = (byte)i;
|
|||
|
|
}
|
|||
|
|
des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
|
|||
|
|
des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
|
|||
|
|
System.IO.MemoryStream ms = new System.IO.MemoryStream();
|
|||
|
|
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
|
|||
|
|
cs.Write(inputByteArray, 0, inputByteArray.Length);
|
|||
|
|
cs.FlushFinalBlock();
|
|||
|
|
return Encoding.Default.GetString(ms.ToArray());
|
|||
|
|
}
|
|||
|
|
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
//protected static string ConnectionString = DecryptDBStr(ConfigurationManager.AppSettings["SQLConnectionString"], "zhangweilong");
|
|||
|
|
protected static string ConnectionString = ConfigurationManager.AppSettings["SQLConnectionString"];
|
|||
|
|
protected static string ConnString = WebConfigurationManager.AppSettings["TJDB"];
|
|||
|
|
protected static string siteConnectionString = ConfigurationManager.AppSettings["SiteConnectionString"];
|
|||
|
|
protected static SqlConnection Connection;
|
|||
|
|
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD><DDBF>Ĵرշ<D8B1><D5B7><EFBFBD>
|
|||
|
|
protected static void Open()
|
|||
|
|
{
|
|||
|
|
if (Connection == null)
|
|||
|
|
{
|
|||
|
|
Connection = new SqlConnection(ConnectionString);
|
|||
|
|
}
|
|||
|
|
if (Connection.State.Equals(ConnectionState.Closed))
|
|||
|
|
{
|
|||
|
|
Connection.Open();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD><DDBF>Ĵرշ<D8B1><D5B7><EFBFBD>
|
|||
|
|
protected static void TJDBOpen()
|
|||
|
|
{
|
|||
|
|
if (Connection == null)
|
|||
|
|
{
|
|||
|
|
Connection = new SqlConnection(ConnString);
|
|||
|
|
}
|
|||
|
|
if (Connection.State.Equals(ConnectionState.Closed))
|
|||
|
|
{
|
|||
|
|
Connection.Open();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
protected static void Close()
|
|||
|
|
{
|
|||
|
|
if (Connection != null)
|
|||
|
|
{
|
|||
|
|
Connection.Close();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><EFBFBD>GridView
|
|||
|
|
public static void BindGridView(string SqlString, GridView MyGvData)
|
|||
|
|
{
|
|||
|
|
MyGvData.DataSource = GetDataSet(SqlString);
|
|||
|
|
MyGvData.DataBind();
|
|||
|
|
}
|
|||
|
|
//<2F><EFBFBD>DropDownList<73><74><EFBFBD>趨Text<78><74>value<75><65>ʾ
|
|||
|
|
public static void BindDropDownList2(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
|
|||
|
|
{
|
|||
|
|
DataSet MyDT =GetDataSet(SqlString);
|
|||
|
|
for(int i=0;i<MyDT.Tables[0].Rows.Count;i++)
|
|||
|
|
{
|
|||
|
|
ListItem MyItem = new ListItem();
|
|||
|
|
MyItem.Text = MyDT.Tables[0].Rows[i][TextStr].ToString();
|
|||
|
|
MyItem.Value = MyDT.Tables[0].Rows[i][ValueStr].ToString();
|
|||
|
|
MyDDL.Items.Add(MyItem);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><EFBFBD>DropDownList<73><74><EFBFBD>趨Text<78><74>value<75><65>ʾ
|
|||
|
|
public static void BindDropDownList(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
|
|||
|
|
{
|
|||
|
|
MyDDL.Items.Clear();
|
|||
|
|
DataSet MyDT = GetDataSet(SqlString);
|
|||
|
|
for (int i = 0; i < MyDT.Tables[0].Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
ListItem MyItem = new ListItem();
|
|||
|
|
MyItem.Text = MyDT.Tables[0].Rows[i][TextStr].ToString();
|
|||
|
|
MyItem.Value = MyDT.Tables[0].Rows[i][ValueStr].ToString();
|
|||
|
|
MyDDL.Items.Add(MyItem);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><EFBFBD>DropDownList<73><74><EFBFBD>趨Text<78><74>value<75><65>ʾ
|
|||
|
|
public static void BindItemList(string SqlString, ListBox MyDDL, string TextStr, string ValueStr)
|
|||
|
|
{
|
|||
|
|
MyDDL.Items.Clear();
|
|||
|
|
DataSet MyDT = GetDataSet(SqlString);
|
|||
|
|
for (int i = 0; i < MyDT.Tables[0].Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
ListItem MyItem = new ListItem();
|
|||
|
|
MyItem.Text = MyDT.Tables[0].Rows[i][TextStr].ToString();
|
|||
|
|
MyItem.Value = MyDT.Tables[0].Rows[i][ValueStr].ToString();
|
|||
|
|
MyDDL.Items.Add(MyItem);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><EFBFBD>DropDownList<73><74><EFBFBD>趨Text<78><74>value<75><65>ʾ
|
|||
|
|
public static void BindDropDownListAddEmpty(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
|
|||
|
|
{
|
|||
|
|
MyDDL.Items.Clear();
|
|||
|
|
ListItem MyItem1 = new ListItem();
|
|||
|
|
MyItem1.Text = "";
|
|||
|
|
MyItem1.Value = "0";
|
|||
|
|
MyDDL.Items.Add(MyItem1);
|
|||
|
|
DataSet MyDT = GetDataSet(SqlString);
|
|||
|
|
for (int i = 0; i < MyDT.Tables[0].Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
ListItem MyItem = new ListItem();
|
|||
|
|
MyItem.Text = MyDT.Tables[0].Rows[i][TextStr].ToString();
|
|||
|
|
MyItem.Value = MyDT.Tables[0].Rows[i][ValueStr].ToString();
|
|||
|
|
MyDDL.Items.Add(MyItem);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><><EFBFBD><EFBFBD>һ<EFBFBD><D2BB><EFBFBD><EFBFBD> | <20>ָ<EFBFBD><D6B8><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD>
|
|||
|
|
public static string GetStringList(string SqlString)
|
|||
|
|
{
|
|||
|
|
string ReturnStr = string.Empty;
|
|||
|
|
DataSet MyDT = GetDataSet(SqlString);
|
|||
|
|
for (int i = 0; i < MyDT.Tables[0].Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (ReturnStr.Length > 0)
|
|||
|
|
{
|
|||
|
|
ReturnStr = ReturnStr + "|" + MyDT.Tables[0].Rows[i][0].ToString();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
ReturnStr = MyDT.Tables[0].Rows[i][0].ToString();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return ReturnStr;
|
|||
|
|
}
|
|||
|
|
//<2F><><EFBFBD>ص<EFBFBD>ǰ<EFBFBD><C7B0><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
|
|||
|
|
public static int GetMaxID(string FieldName, string TableName)
|
|||
|
|
{
|
|||
|
|
int MyReturn = 0;
|
|||
|
|
DataSet MyDT = GetDataSet("select max(" + FieldName + ") from " + TableName);
|
|||
|
|
if (MyDT.Tables[0].Rows.Count>0)
|
|||
|
|
{
|
|||
|
|
MyReturn = int.Parse(MyDT.Tables[0].Rows[0][0].ToString());
|
|||
|
|
}
|
|||
|
|
return MyReturn;
|
|||
|
|
}
|
|||
|
|
//<2F>ж<EFBFBD><D0B6><EFBFBD>Sql<71><6C>ѯ<EFBFBD><D1AF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD>,true<75><65>ʾ<EFBFBD><CABE><EFBFBD>ڣ<EFBFBD>False<73><65>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
public static bool Exists(string strSql)
|
|||
|
|
{
|
|||
|
|
object obj = DbHelperSQL.GetSingle(strSql);
|
|||
|
|
int cmdresult;
|
|||
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|||
|
|
{
|
|||
|
|
cmdresult = 0;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
cmdresult = int.Parse(obj.ToString());
|
|||
|
|
}
|
|||
|
|
if (cmdresult == 0)
|
|||
|
|
{
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
|
|||
|
|
{
|
|||
|
|
object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
|
|||
|
|
int cmdresult;
|
|||
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|||
|
|
{
|
|||
|
|
cmdresult = 0;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
cmdresult = int.Parse(obj.ToString());
|
|||
|
|
}
|
|||
|
|
if (cmdresult == 0)
|
|||
|
|
{
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//<2F><><EFBFBD><EFBFBD>SqlDataReader<65><72><EFBFBD>ݼ<EFBFBD>,ʹ<><CAB9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ǵùر<C3B9>SqlDataReader
|
|||
|
|
//public static SqlDataReader GetDataReader(string SqlString)
|
|||
|
|
//{
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// Open();
|
|||
|
|
// SqlCommand cmd = new SqlCommand(SqlString, Connection);
|
|||
|
|
// return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
|
|||
|
|
// }
|
|||
|
|
// catch (System.Data.SqlClient.SqlException ex)
|
|||
|
|
// {
|
|||
|
|
// //throw new Exception(ex.Message);
|
|||
|
|
// return null;
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB>DataSet<65><74>
|
|||
|
|
public static DataSet GetDataSet(string SqlString)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(SqlString, connection))
|
|||
|
|
{
|
|||
|
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
da.Fill(ds, "ds");
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
connection.Close();
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB>DataSet<65><74>
|
|||
|
|
public static DataSet GetDataSetTJDB(string SqlString)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnString))
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(SqlString, connection))
|
|||
|
|
{
|
|||
|
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
da.Fill(ds, "ds");
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
connection.Close();
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB>DataSet<65><74>
|
|||
|
|
public static DataSet GetDataSetSite(string SqlString)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(siteConnectionString))
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(SqlString, connection))
|
|||
|
|
{
|
|||
|
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
da.Fill(ds, "ds");
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
connection.Close();
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB>DataTable<6C><65>
|
|||
|
|
public static DataTable GetDataTable(string SqlString)
|
|||
|
|
{
|
|||
|
|
DataSet dataset = GetDataSet(SqlString);
|
|||
|
|
return dataset.Tables[0];
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>С<EFBFBD>
|
|||
|
|
public static string GetSHSL(string SqlString)
|
|||
|
|
{
|
|||
|
|
DataSet dataset = GetDataSet(SqlString);
|
|||
|
|
if (dataset.Tables[0].Rows.Count > 0)
|
|||
|
|
{
|
|||
|
|
return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return "";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>е<EFBFBD>INTֵ<54><D6B5>
|
|||
|
|
public static string GetSHSLInt(string SqlString)
|
|||
|
|
{
|
|||
|
|
DataSet dataset = GetDataSet(SqlString);
|
|||
|
|
if (dataset.Tables[0].Rows.Count > 0)
|
|||
|
|
{
|
|||
|
|
return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return "0";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD>ݣ<EFBFBD><DDA3><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB>DataRow<6F><77>
|
|||
|
|
public static DataRow GetDataRow(string SqlString)
|
|||
|
|
{
|
|||
|
|
DataSet dataset = GetDataSet(SqlString);
|
|||
|
|
if (dataset.Tables[0].Rows.Count > 0)
|
|||
|
|
{
|
|||
|
|
return dataset.Tables[0].Rows[0];
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD><D6B4>Sql<71><6C><EFBFBD>䡣<EFBFBD><E4A1A3>Update<74><65>Insert<72><74>DeleteΪӰ<CEAA>쵽<EFBFBD><ECB5BD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊ-1
|
|||
|
|
public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)
|
|||
|
|
{
|
|||
|
|
int count = -1;
|
|||
|
|
SqlConnection connectiontemp = new SqlConnection(ConnectionString);
|
|||
|
|
connectiontemp.Open();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
SqlCommand cmd = new SqlCommand(SqlString, connectiontemp);
|
|||
|
|
foreach (DictionaryEntry item in MyHashTb)
|
|||
|
|
{
|
|||
|
|
string[] CanShu = item.Key.ToString().Split('|');
|
|||
|
|
if (CanShu[1].ToString().Trim() == "string")
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
|
|||
|
|
}
|
|||
|
|
else if (CanShu[1].ToString().Trim() == "int")
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(CanShu[0], SqlDbType.Int);
|
|||
|
|
}
|
|||
|
|
else if (CanShu[1].ToString().Trim() == "text")
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(CanShu[0], SqlDbType.Text);
|
|||
|
|
}
|
|||
|
|
else if (CanShu[1].ToString().Trim() == "datetime")
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(CanShu[0], SqlDbType.DateTime);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
|
|||
|
|
}
|
|||
|
|
cmd.Parameters[CanShu[0]].Value = item.Value.ToString();
|
|||
|
|
}
|
|||
|
|
count = cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
count = -1;
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
connectiontemp.Close();
|
|||
|
|
}
|
|||
|
|
return count;
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD><D6B4>Sql<71><6C><EFBFBD>䡣<EFBFBD><E4A1A3>Update<74><65>Insert<72><74>DeleteΪӰ<CEAA>쵽<EFBFBD><ECB5BD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊ-1
|
|||
|
|
public static int ExecuteSQL(String SqlString)
|
|||
|
|
{
|
|||
|
|
int count = -1;
|
|||
|
|
SqlConnection connectionTemp = new SqlConnection(ConnectionString);
|
|||
|
|
connectionTemp.Open();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
SqlCommand cmd = new SqlCommand(SqlString, connectionTemp);
|
|||
|
|
count = cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
count = -1;
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
connectionTemp.Close();
|
|||
|
|
}
|
|||
|
|
return count;
|
|||
|
|
}
|
|||
|
|
// <20><><EFBFBD>з<EFBFBD><D0B7><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD><D6B4>һ<EFBFBD><D2BB>Sql<71><6C><EFBFBD>䡣<EFBFBD><E4A1A3><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7>ɹ<EFBFBD>,<2C><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>쳣ʱ<ECB3A3>ع<EFBFBD><D8B9><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
public static bool ExecuteSQL(string[] SqlStrings)
|
|||
|
|
{
|
|||
|
|
bool success = true;
|
|||
|
|
SqlConnection connectionTemp = new SqlConnection(ConnectionString);
|
|||
|
|
connectionTemp.Open();
|
|||
|
|
SqlCommand cmd = new SqlCommand();
|
|||
|
|
SqlTransaction trans = Connection.BeginTransaction();
|
|||
|
|
cmd.Connection = connectionTemp;
|
|||
|
|
cmd.Transaction = trans;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
foreach (string str in SqlStrings)
|
|||
|
|
{
|
|||
|
|
cmd.CommandText = str;
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
trans.Commit();
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
success = false;
|
|||
|
|
trans.Rollback();
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
connectionTemp.Close();
|
|||
|
|
}
|
|||
|
|
return success;
|
|||
|
|
}
|
|||
|
|
// ִ<><D6B4>һ<EFBFBD><D2BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯ<EFBFBD><D1AF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD><E4A3AC><EFBFBD>ز<EFBFBD>ѯ<EFBFBD><D1AF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>object<63><74><EFBFBD><EFBFBD>
|
|||
|
|
public static object GetSingle(string SQLString)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
object obj = cmd.ExecuteScalar();
|
|||
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return obj;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException e)
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return null;
|
|||
|
|
//throw e;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public static object GetSingle(string SQLString, int Times)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
cmd.CommandTimeout = Times;
|
|||
|
|
object obj = cmd.ExecuteScalar();
|
|||
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return obj;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException e)
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
//throw e;
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
using (SqlCommand cmd = new SqlCommand())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|||
|
|
object obj = cmd.ExecuteScalar();
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
return obj;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException e)
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
//throw e;
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// ִ<><D6B4>SQL<51><4C><EFBFBD>䣬<EFBFBD><E4A3AC><EFBFBD><EFBFBD>Ӱ<EFBFBD><D3B0><EFBFBD>ļ<EFBFBD>¼<EFBFBD><C2BC>
|
|||
|
|
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
using (SqlCommand cmd = new SqlCommand())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|||
|
|
int rows = cmd.ExecuteNonQuery();
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
connection.Close();
|
|||
|
|
return rows;
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException e)
|
|||
|
|
{
|
|||
|
|
connection.Close();
|
|||
|
|
//throw e;
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//ִ<>в<EFBFBD>ѯ<EFBFBD><D1AF><EFBFBD>䣬<EFBFBD><E4A3AC><EFBFBD><EFBFBD>DataSet
|
|||
|
|
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
|
|||
|
|
{
|
|||
|
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
|||
|
|
{
|
|||
|
|
SqlCommand cmd = new SqlCommand();
|
|||
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|||
|
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
da.Fill(ds, "ds");
|
|||
|
|
cmd.Parameters.Clear();
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (System.Data.SqlClient.SqlException ex)
|
|||
|
|
{
|
|||
|
|
//throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
connection.Close();
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType;
|
|||
|
|
if (cmdParms != null)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
|
|||
|
|
foreach (SqlParameter parameter in cmdParms)
|
|||
|
|
{
|
|||
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
|||
|
|
(parameter.Value == null))
|
|||
|
|
{
|
|||
|
|
parameter.Value = DBNull.Value;
|
|||
|
|
}
|
|||
|
|
cmd.Parameters.Add(parameter);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|