1.建一个通用的处理数据的类
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace Document
{
/// <summary>
/// Summary description for DataHelper.
/// </summary>
public class DataHelper
{
public DataHelper()
{
//
// TODO: Add constructor logic here
//
}
public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
#region GetDataSet
public static DataSet GetDataSet(string sql)
{
SqlDataAdapter sda =new SqlDataAdapter(sql,ConnectionString);
DataSet ds=new DataSet();
sda.Fill(ds);
return ds;
}
#endregion
#region ExecCommand
public static int ExecCommand(SqlCommand sqlcom)
{
SqlConnection conn=new SqlConnection(ConnectionString);
sqlcom.Connection =conn;
conn.Open();
try
{
int rtn=sqlcom.ExecuteNonQuery();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return 0;
}
public static int ExecCommand(string sql)
{
if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);
SqlCommand sqlcom=new SqlCommand(sql);
return ExecCommand(sqlcom);
}
#endregion
#region ExecuteScalar
public static object ExecuteScalar(string sql)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
conn.Open();
try
{
object rtn=sqlcom.ExecuteScalar ();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return null;
}
#endregion
#region ExecSPCommand
public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch(Exception ex)
{
string s=ex.Message ;
}
finally
{
conn.Close();
}
}
#endregion
#region ExecSPDataSet
public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=sqlcom;
DataSet ds=new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion
#region DbType
private static System.Data.DbType GetDbType(Type type)
{
DbType result = DbType.String;
if( type.Equals(typeof(int)) || type.IsEnum)
result = DbType.Int32;
else if( type.Equals(typeof(long)))
result = DbType.Int32;
else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
result = DbType.Decimal;
else if( type.Equals(typeof(DateTime)))
result = DbType.DateTime;
else if( type.Equals(typeof(bool)))
result = DbType.Boolean;
else if( type.Equals(typeof(string) ) )
result = DbType.String;
else if( type.Equals(typeof(decimal)))
result = DbType.Decimal;
else if( type.Equals(typeof(byte[])))
result = DbType.Binary;
else if( type.Equals(typeof(Guid)))
result = DbType.Guid;
return result;
}
#endregion
#region UpdateTable
public static void UpdateTable(DataTable dt,string TableName,string KeyName)
{
foreach(DataRow dr in dt.Rows)
{
updateRow(dr,TableName,KeyName);
}
}
#endregion
InsertTable
DeleteTable
#region updateRow
private static void updateRow(DataRow dr,string TableName,string KeyName)
{
if (dr[KeyName]==DBNull.Value )
{
throw new Exception(KeyName +"的值不能为空");
}
if (dr.RowState ==DataRowState.Deleted)
{
deleteRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Modified )
{
midifyRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Added )
{
insertRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Unchanged )
{
midifyRow(dr,TableName,KeyName);
}
}
#endregion
#region deleteRow
private static void deleteRow(DataRow dr,string TableName,string KeyName)
{
string sql="Delete {0} where {1} =@{1}";
DataTable dtb=dr.Table ;
sql=string.Format(sql,TableName,KeyName);
SqlCommand sqlcom=new SqlCommand(sql);
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ KeyName;
iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType);
iparam.Value = dr[KeyName];
sqlcom.Parameters .Add(iparam);
ExecCommand(sqlcom);
}
#endregion
midifyRow
insertRow
}
}
2..调用范例using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace Document
{
/// <summary>
/// Summary description for DataHelper.
/// </summary>
public class DataHelper
{
public DataHelper()
{
//
// TODO: Add constructor logic here
//
}
public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
#region GetDataSet
public static DataSet GetDataSet(string sql)
{
SqlDataAdapter sda =new SqlDataAdapter(sql,ConnectionString);
DataSet ds=new DataSet();
sda.Fill(ds);
return ds;
}
#endregion
#region ExecCommand
public static int ExecCommand(SqlCommand sqlcom)
{
SqlConnection conn=new SqlConnection(ConnectionString);
sqlcom.Connection =conn;
conn.Open();
try
{
int rtn=sqlcom.ExecuteNonQuery();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return 0;
}
public static int ExecCommand(string sql)
{
if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);
SqlCommand sqlcom=new SqlCommand(sql);
return ExecCommand(sqlcom);
}
#endregion
#region ExecuteScalar
public static object ExecuteScalar(string sql)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
conn.Open();
try
{
object rtn=sqlcom.ExecuteScalar ();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return null;
}
#endregion
#region ExecSPCommand
public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch(Exception ex)
{
string s=ex.Message ;
}
finally
{
conn.Close();
}
}
#endregion
#region ExecSPDataSet
public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=sqlcom;
DataSet ds=new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion
#region DbType
private static System.Data.DbType GetDbType(Type type)
{
DbType result = DbType.String;
if( type.Equals(typeof(int)) || type.IsEnum)
result = DbType.Int32;
else if( type.Equals(typeof(long)))
result = DbType.Int32;
else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
result = DbType.Decimal;
else if( type.Equals(typeof(DateTime)))
result = DbType.DateTime;
else if( type.Equals(typeof(bool)))
result = DbType.Boolean;
else if( type.Equals(typeof(string) ) )
result = DbType.String;
else if( type.Equals(typeof(decimal)))
result = DbType.Decimal;
else if( type.Equals(typeof(byte[])))
result = DbType.Binary;
else if( type.Equals(typeof(Guid)))
result = DbType.Guid;
return result;
}
#endregion
#region UpdateTable
public static void UpdateTable(DataTable dt,string TableName,string KeyName)
{
foreach(DataRow dr in dt.Rows)
{
updateRow(dr,TableName,KeyName);
}
}
#endregion
InsertTable
DeleteTable
#region updateRow
private static void updateRow(DataRow dr,string TableName,string KeyName)
{
if (dr[KeyName]==DBNull.Value )
{
throw new Exception(KeyName +"的值不能为空");
}
if (dr.RowState ==DataRowState.Deleted)
{
deleteRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Modified )
{
midifyRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Added )
{
insertRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Unchanged )
{
midifyRow(dr,TableName,KeyName);
}
}
#endregion
#region deleteRow
private static void deleteRow(DataRow dr,string TableName,string KeyName)
{
string sql="Delete {0} where {1} =@{1}";
DataTable dtb=dr.Table ;
sql=string.Format(sql,TableName,KeyName);
SqlCommand sqlcom=new SqlCommand(sql);
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ KeyName;
iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType);
iparam.Value = dr[KeyName];
sqlcom.Parameters .Add(iparam);
ExecCommand(sqlcom);
}
#endregion
midifyRow
insertRow
}
}
Insert
Update
Delete
Update
Delete