DataHelper

1.建一个通用的处理数据的类

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace Document
{
/**////<summary>
///SummarydescriptionforDataHelper.
///</summary>
publicclassDataHelper
{
publicDataHelper()
{
//
//TODO:Addconstructorlogichere
//
}
publicstaticstringConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

GetDataSet#regionGetDataSet
publicstaticDataSetGetDataSet(stringsql)
{
SqlDataAdaptersda=newSqlDataAdapter(sql,ConnectionString);
DataSetds=newDataSet();
sda.Fill(ds);
returnds;
}
#endregion

ExecCommand#regionExecCommand
publicstaticintExecCommand(SqlCommandsqlcom)
{
SqlConnectionconn=newSqlConnection(ConnectionString);
sqlcom.Connection=conn;
conn.Open();
try
{
intrtn=sqlcom.ExecuteNonQuery();
returnrtn;
}
catch(Exceptionex)
{
throwex;
}
finally
{
conn.Close();
}
return0;

}
publicstaticintExecCommand(stringsql)
{
if(sql.EndsWith(","))sql=sql.Substring(0,sql.Length-1);

SqlCommandsqlcom=newSqlCommand(sql);
returnExecCommand(sqlcom);
}
#endregion

ExecuteScalar#regionExecuteScalar
publicstaticobjectExecuteScalar(stringsql)
{
SqlConnectionconn=newSqlConnection(ConnectionString);
SqlCommandsqlcom=newSqlCommand(sql,conn);
conn.Open();
try
{
objectrtn=sqlcom.ExecuteScalar();
returnrtn;
}
catch(Exceptionex)
{
throwex;
}
finally
{
conn.Close();
}
returnnull;
}
#endregion

ExecSPCommand#regionExecSPCommand
publicstaticvoidExecSPCommand(stringsql,System.Data.IDataParameter[]paramers)
{
SqlConnectionconn=newSqlConnection(ConnectionString);
SqlCommandsqlcom=newSqlCommand(sql,conn);
sqlcom.CommandType=CommandType.StoredProcedure;

foreach(System.Data.IDataParameterparamerinparamers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch(Exceptionex)
{
strings=ex.Message;
}
finally
{
conn.Close();
}
}
#endregion

ExecSPDataSet#regionExecSPDataSet
publicstaticDataSetExecSPDataSet(stringsql,System.Data.IDataParameter[]paramers)
{
SqlConnectionconn=newSqlConnection(ConnectionString);
SqlCommandsqlcom=newSqlCommand(sql,conn);
sqlcom.CommandType=CommandType.StoredProcedure;

foreach(System.Data.IDataParameterparamerinparamers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();

SqlDataAdapterda=newSqlDataAdapter();
da.SelectCommand=sqlcom;
DataSetds=newDataSet();
da.Fill(ds);

conn.Close();
returnds;
}

#endregion

DbType#regionDbType
privatestaticSystem.Data.DbTypeGetDbType(Typetype)
{
DbTyperesult=DbType.String;
if(type.Equals(typeof(int))||type.IsEnum)
result=DbType.Int32;
elseif(type.Equals(typeof(long)))
result=DbType.Int32;
elseif(type.Equals(typeof(double))||type.Equals(typeof(Double)))
result=DbType.Decimal;
elseif(type.Equals(typeof(DateTime)))
result=DbType.DateTime;
elseif(type.Equals(typeof(bool)))
result=DbType.Boolean;
elseif(type.Equals(typeof(string)))
result=DbType.String;
elseif(type.Equals(typeof(decimal)))
result=DbType.Decimal;
elseif(type.Equals(typeof(byte[])))
result=DbType.Binary;
elseif(type.Equals(typeof(Guid)))
result=DbType.Guid;

returnresult;

}

#endregion

UpdateTable#regionUpdateTable
publicstaticvoidUpdateTable(DataTabledt,stringTableName,stringKeyName)
{
foreach(DataRowdrindt.Rows)
{
updateRow(dr,TableName,KeyName);
}
}
#endregion

InsertTable#regionInsertTable
//用于主键是数据库表名+ID类型的
publicstaticvoidInsertTable(DataTabledt)
{
stringTableName="["+dt.TableName+"]";
stringKeyName=dt.TableName+"ID";
foreach(DataRowdrindt.Rows)
{
insertRow(dr,TableName,KeyName);
}
}
//用于主键是任意类型的
publicstaticvoidInsertTable(DataTabledt,stringKeyName)
{
stringTableName="["+dt.TableName+"]";
foreach(DataRowdrindt.Rows)
{
insertRow(dr,TableName,KeyName);
}
}
#endregion

DeleteTable#regionDeleteTable
publicstaticvoidDeleteTable(DataTabledt,stringKeyName)
{
stringTableName="["+dt.TableName+"]";
foreach(DataRowdrindt.Rows)
{
deleteRow(dr,TableName,KeyName);
}
}
#endregion

update