C#数据库操作类完整通用.docx
《C#数据库操作类完整通用.docx》由会员分享,可在线阅读,更多相关《C#数据库操作类完整通用.docx(14页珍藏版)》请在冰豆网上搜索。
C#数据库操作类完整通用
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingSystem.Configuration;
usingSystem.Data;
namespaceMSSQL数据库操作类
{
publicclassSqlServer
{
SqlConnection_con=newSqlConnection();
SqlCommand_cmd=newSqlCommand();
SqlDataAdapter_sda=newSqlDataAdapter();
publicSqlServer()
:
this(ConfigurationManager.ConnectionStrings["MSSQLConStr"].ConnectionString)
{
}
publicSqlServer(stringconnectionString)
{
this._con.ConnectionString=connectionString;
this._cmd.Connection=this._con;
}
publicvoidReset()
{
this._cmd.Connection=this._con;
this._cmd.CommandType=CommandType.Text;
this.SQL=string.Empty;
this.Parameters.Clear();
}
publicvoidOpen()
{
if(this._con.State==ConnectionState.Closed)this._con.Open();
}
publicvoidClose()
{
if(this._con.State==ConnectionState.Open)this._con.Close();
}
publicCommandTypeCommandType
{
get
{
returnthis._cmd.CommandType;
}
set
{
this._cmd.CommandType=value;
}
}
publicstringSQL
{
get
{
returnthis._cmd.CommandText;
}
set
{
this._cmd.CommandText=value;
}
}
publicSqlParameterCollectionParameters
{
get
{
returnthis._cmd.Parameters;
}
}
publicstringGetDeleteSQL(stringtableName,stringcondition)
{
if(!
string.IsNullOrEmpty(condition))
{
condition="WHERE"+condition;
}
returnstring.Format("DELETEFROM{0}{1}",tableName,condition);
}
//SELECTTOP个数fieldsFROMtableNameWHERE条件
publicstringGetSelectSQL(stringtable,stringcondition,inttop,stringfields,stringorderBy)
{
#region不太好的版本
//stringsql="SELECT";
//if(top>1)
//{
//sql+="TOP"+top.ToString()+"";
//}
//if(!
string.IsNullOrEmpty(fields))
//{
//sql+=fields;
//}
//else
//{
//sql+="*FROM";
//}
//sql+=tableName;
//if(!
string.IsNullOrEmpty(condition))
//{
//condition="WHERE"+condition;
//}
//sql+=condition;
//if(!
string.IsNullOrEmpty(orderBy))
//{
//orderBy="ORDERBY"+orderBy;
//}
//sql+=orderBy;
#endregion
stringt=top>0?
"TOP"+top.ToString():
"";
if(string.IsNullOrEmpty(fields))
{
fields="*";
}
if(!
string.IsNullOrEmpty(condition))
{
condition="WHERE"+condition;
}
if(!
string.IsNullOrEmpty(orderBy))
{
orderBy="ORDERBY"+orderBy;
}
stringsql=string.Format("SELECT{0}{1}FROM{2}{3}{4}",t,fields,table,condition,orderBy);
returnsql;
}
publicstringGetSelectSQL(stringtable,stringcondition,stringfields,stringorderBy)
{
returnthis.GetSelectSQL(table,condition,0,fields,orderBy);
}
publicstringGetGagingSelectSQL(stringtable,stringcondition,stringfields,stringorderBy)
{
stringsql=@"SELECT*FROM(
SELECTROW_NUMBER()OVER(ORDERBY{0})ASRowNum,{1}FROM{2}{3}
)AST
WHERERowNumBETWEEN(@CurrentPage-1)*@PageSize+1AND@CurrentPage*@PageSizeORDERBY{0}";
if(!
string.IsNullOrEmpty(condition))
{
condition="WHERE"+condition;
}
if(string.IsNullOrEmpty(fields))
{
fields="*";
}
returnstring.Format(sql,orderBy,fields,table,condition);
}
publicstringGetInsertSQL(stringtable)
{
//INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES(@Sno,@Sname,@Ssex,@Sage,@Sdept)
stringsql="INSERTINTO{0}({1})VALUES({2})";
stringfield=string.Empty;
stringparameter=string.Empty;
foreach(SqlParametersprinthis.Parameters)
{
field+=spr.ParameterName.Remove(0,1)+",";
parameter+=spr.ParameterName+",";
}
field=field.Remove(field.Length-1,1);
parameter=parameter.Remove(parameter.Length-1,1);
returnstring.Format(sql,table,field,parameter);
}
publicstringGetUpdateSQL(stringtable,stringcondition)
{
//UPDATEStudentSETSno=@Sno,Sname=@SnameWHERESno=@SnoAND.....
stringsql="UPDATE{0}SET{1}{2}";
stringstr=string.Empty;
foreach(SqlParametersprinthis.Parameters)
{
str+=spr.ParameterName.Remove(0,1)+"="+spr.ParameterName+",";
}
str=str.Remove(str.Length-1,1);
if(!
string.IsNullOrEmpty(condition))
{
condition="WHERE"+condition;
}
returnstring.Format(sql,table,str,condition);
}
///
///添加SqlParameter,需要5个属性值
///
///参数名
///参数数据类型
///大小
///源列名
///值
///被添加进去的参数对象
publicSqlParameterAddSqlParameter(stringparameterName,SqlDbTypesqlDbType,intsize,stringsourceColumn,objectvalue)
{
SqlParameterspr=newSqlParameter();
spr.ParameterName=parameterName;
spr.SqlDbType=sqlDbType;
if(size>0)
{
spr.Size=size;
}
if(!
string.IsNullOrEmpty(sourceColumn))
{
spr.SourceColumn=sourceColumn;
}
spr.Value=(value==null)?
DBNull.Value:
value;
returnthis._cmd.Parameters.Add(spr);
}
///
///添加SqlParameter,需要4个属性值
///
///参数名
///参数数据类型
///源列名
///值
///被添加进去的参数对象
publicSqlParameterAddSqlParameter(stringparameterName,SqlDbTypesqlDbType,stringsourceColumn,objectvalue)
{
returnthis.AddSqlParameter(parameterName,sqlDbType,0,sourceColumn,value);
}
///
///添加SqlParameter,需要3个属性值
///
///参数名
///参数数据类型
///值
///被添加进去的参数对象
publicSqlParameterAddSqlParameter(stringparameterName,SqlDbTypesqlDbType,objectvalue)
{
returnthis.AddSqlParameter(parameterName,sqlDbType,null,value);
}
///
///添加SqlParameter,需要2个属性值
///
///参数名
///参数数据类型
///值
///被添加进去的参数对象
publicSqlParameterAddSqlParameter(stringparameterName,objectvalue)
{
SqlParameterspr=newSqlParameter();
spr.ParameterName=parameterName;
spr.Value=(value==null)?
DBNull.Value:
value;
returnthis._cmd.Parameters.Add(spr);
}
publicvoidClearSqlParameter()
{
this._cmd.Parameters.Clear();
}
#region执行UPDATE、INSERT、DELETE,返回受影响的行数
publicintExcuteNonQuery()
{
try
{
this.Open();
return_cmd.ExecuteNonQuery();
}
catch(Exceptionex)
{
throwex;
}
finally
{
this.Close();
}
}
#endregion
#region执行SELECTCOUNT(*)FROM....类似的SQL语句,返回首行首列
publicobjectExcuteScalar()
{
try
{
this.Open();
return_cmd.ExecuteScalar();
}
catch(Exceptionex)
{
throwex;
}
finally
{
this.Close();
}
}
#endregion
#region执行SELECT属性列表FROM....类似的SQL语句,返回首行首列
publicSqlDataReaderExcuteReader()
{
try
{
this.Open();
return_cmd.ExecuteReader();
}
catch(Exceptionex)
{
throwex;
}
finally
{
//if(con.State==ConnectionState.Open)con.Close();//可以使用委托机制来解决数据无法关闭的问题
}
}
#endregion
#region执行SELECT属性列表FROM....类似的SQL语句,返回一个内存中的数据库
publicDataSetGetDataSet()
{
try
{
this._sda.SelectCommand=this._cmd;
DataSetds=newDataSet();
this._sda.Fill(ds);
returnds;
}
catch(Exceptionex)
{
throwex;
}
finally
{
}
}
#endregion
#region有缺陷的写法,返回的数据表不能和其它的数据库任意进行合并等操作,因为其和ds有关系
//publicDataTableGetDataTable(stringSQL)
//{
//DataSetds=this.GetDataSet(SQL);
//returnds.Tables[0];
//}
#endregion
#region执行SELECT属性列表FROM....类似的SQL语句,返回一个内存中的数据表
publicDataTableGetDataTable()
{
try
{
this._sda.SelectCommand=this._cmd;
DataTabledt=newDataTable();
this._sda.Fill(dt);
returndt;
}
catch(Exceptionex)
{
throwex;
}
finally
{
}
}
#endregion
}
}