SqlHelper
using system;
using system.collections.generic;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.linq;
using system.reflection;
using system.text;
namespace mz.models.dal
{
//可更改为mysql
public class sqlhelper
{
#region fields
private string connectstring = configurationmanager.connectionstrings["connstr"].tostring();
#endregion
#region constructors
public sqlhelper()
{
}
public sqlhelper(string connectstring)
{
this.connectstring = connectstring;
}
#endregion
#region property connectionstring
public string connectionstring
{
get { return this.connectstring; }
}
#endregion
#region method executenonquery
public int executenonquery(string commandtext)
{
return this.executenonquery(commandtext, commandtype.text, null);
}
public int executenonquery(string commandtext, sqlparameter[] paras)
{
return this.executenonquery(commandtext, commandtype.text, paras);
}
public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
int result = 0;
using (sqlconnection connection = new sqlconnection(this.connectstring))
{
connection.open();
result = this.executenonquery(commandtext, commandtype, paras, connection);
connection.close();
}
return result;
}
public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
int result = command.executenonquery();
command.dispose();
return result;
}
public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
int result = command.executenonquery();
command.dispose();
return result;
}
#endregion
#region method executescalar
public int executescalar(string commandtext)
{
return this.executescalar(commandtext, commandtype.text, null);
}
public int executescalar(string commandtext, sqlparameter[] paras)
{
return this.executescalar(commandtext, commandtype.text, paras);
}
public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
int result = 0;
using (sqlconnection connection = new sqlconnection(this.connectstring))
{
connection.open();
result = executescalar(commandtext, commandtype, paras, connection);
connection.close();
}
return result;
}
public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
object result = command.executescalar();
command.dispose();
if (result == null || result + "" == "")
return 0;
return convert.toint32(result);
}
public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
object result = command.executescalar();
command.dispose();
if (result == null)
return 0;
return (int)result;
}
#endregion
#region method executeobjectscalar
public object executeobjectscalar(string commandtext)
{
return this.executeobjectscalar(commandtext, commandtype.text, null);
}
public object executeobjectscalar(string commandtext, sqlparameter[] paras)
{
return this.executeobjectscalar(commandtext, commandtype.text, paras);
}
public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
object result;
using (sqlconnection connection = new sqlconnection(this.connectstring))
{
connection.open();
result = executeobjectscalar(commandtext, commandtype, paras, connection);
connection.close();
}
return result;
}
public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
object result = command.executescalar();
command.dispose();
if (result == null || result + "" == "")
return null;
return result;
}
public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
object result = command.executescalar();
command.dispose();
if (result == null)
return null;
return result;
}
#endregion
#region method executestringscalar
public string executestringscalar(string commandtext)
{
return this.executestringscalar(commandtext, commandtype.text, null);
}
public string executestringscalar(string commandtext, sqlparameter[] paras)
{
return this.executestringscalar(commandtext, commandtype.text, paras);
}
public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
string result = "";
using (sqlconnection connection = new sqlconnection(this.connectstring))
{
connection.open();
result = executestringscalar(commandtext, commandtype, paras, connection);
connection.close();
}
return result;
}
public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
object result = command.executescalar();
command.dispose();
if (result == null)
return "";
return result.tostring();
}
public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
object result = command.executescalar();
command.dispose();
if (result == null)
return "";
return result.tostring();
}
#endregion
#region method executedatatable
public datatable executedatatable(string commandtext)
{
return this.executedatatable(commandtext, commandtype.text, null);
}
public datatable executedatatable(string commandtext, sqlparameter[] paras)
{
return this.executedatatable(commandtext, commandtype.text, paras);
}
public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
datatable dt = null;
using (sqlconnection connection = new sqlconnection(this.connectstring))
{
connection.open();
dt = this.executedatatable(commandtext, commandtype, paras, connection);
connection.close();
}
return dt;
}
public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
datatable dt = new datatable();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
sqldataadapter adapter = new sqldataadapter(command);
adapter.fill(dt);
command.dispose();
adapter.dispose();
return dt;
}
public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
datatable dt = new datatable();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
sqldataadapter adapter = new sqldataadapter(command);
adapter.fill(dt);
command.dispose();
adapter.dispose();
return dt;
}
#endregion
#region method executedataset
public dataset executedataset(string commandtext)
{
return this.executedataset(commandtext, commandtype.text, null);
}
public dataset executedataset(string commandtext, sqlparameter[] paras)
{
return this.executedataset(commandtext, commandtype.text, paras);
}
public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
dataset ds = null;
using (sqlconnection connection = new sqlconnection(connectstring))
{
connection.open();
ds = this.executedataset(commandtext, commandtype, paras, connection);
connection.close();
}
return ds;
}
public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
dataset ds = new dataset();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
sqldataadapter adapter = new sqldataadapter(command);
adapter.fill(ds);
command.dispose();
adapter.dispose();
return ds;
}
public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
dataset ds = new dataset();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
sqldataadapter adapter = new sqldataadapter(command);
adapter.fill(ds);
command.dispose();
adapter.dispose();
return ds;
}
#endregion
#region method executeentity
private static t executedatareader<t>(sqldatareader dr)
{
t obj = default(t);
type type = typeof(t);
propertyinfo[] propertyinfos = type.getproperties();
int columncount = dr.fieldcount;
obj = activator.createinstance<t>();
foreach (propertyinfo propertyinfo in propertyinfos)
{
string propertyname = propertyinfo.name;
for (int i = 0; i < columncount; i++)
{
string columnname = dr.getname(i);
if (string.compare(propertyname, columnname, true) == 0)
{
object value = dr.getvalue(i);
if (value != null && value != dbnull.value)
{
propertyinfo.setvalue(obj, value, null);
}
break;
}
}
}
return obj;
}
public t executeentity<t>(string commandtext)
{
return this.executeentity<t>(commandtext, commandtype.text, null);
}
public t executeentity<t>(string commandtext, sqlparameter[] paras)
{
return this.executeentity<t>(commandtext, commandtype.text, paras);
}
public t executeentity<t>(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
t obj = default(t);
using (sqlconnection connection = new sqlconnection(connectstring))
{
connection.open();
obj = this.executeentity<t>(commandtext, commandtype, paras, connection);
connection.close();
}
return obj;
}
public t executeentity<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
t obj = default(t);
using (sqlcommand cmd = new sqlcommand(commandtext, connection))
{
cmd.commandtype = commandtype;
if(paras!=null)
cmd.parameters.addrange(paras);
connection.close();
connection.open();
using (sqldatareader dr = cmd.executereader(commandbehavior.closeconnection))
{
while (dr.read())
{
obj = executedatareader<t>(dr);
break;
}
}
}
return obj;
}
#endregion
#region method executelist
public list<t> executelist<t>(string commandtext)
{
return this.executelist<t>(commandtext, commandtype.text, null);
}
public list<t> executelist<t>(string commandtext, sqlparameter[] paras)
{
return this.executelist<t>(commandtext, commandtype.text, paras);
}
public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras)
{
list<t> list = new list<t>();
using (sqlconnection connection = new sqlconnection(connectstring))
{
connection.open();
list = this.executelist<t>(commandtext, commandtype, paras, connection);
connection.close();
}
return list;
}
public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
list<t> list = new list<t>();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
using (sqldatareader dr = command.executereader(commandbehavior.closeconnection))
{
while (dr.read())
{
t obj = executedatareader<t>(dr);
list.add(obj);
}
}
command.dispose();
return list;
}
public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
list<t> list = new list<t>();
sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
using (sqldatareader dr = command.executereader(commandbehavior.closeconnection))
{
while (dr.read())
{
t obj = executedatareader<t>(dr);
list.add(obj);
}
}
command.dispose();
return list;
}
#endregion
#region method insertbatch
public int insertbatch(string commandtext, datatable data, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = this.createcommandhelper(commandtext, commandtype.text, paras, trans);
sqldataadapter adapter = new sqldataadapter();
adapter.insertcommand = command;
int result = adapter.update(data);
adapter.dispose();
command.dispose();
return result;
}
#endregion
#region private method createcommandhelper
private sqlcommand createcommandhelper(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
{
sqlcommand command = new sqlcommand();
command.commandtext = commandtext;
command.commandtype = commandtype;
command.connection = connection;
if (paras != null && paras.length > 0)
{
foreach (sqlparameter p in paras)
{
/*update 修改无法使用 parameterdirection.output 来输出值的bug*/
//sqlparameter paranew = new sqlparameter();
if (p != null)
{
// check for derived output value with no value assigned
if ((p.direction == parameterdirection.inputoutput ||
p.direction == parameterdirection.input) &&
(p.value == null))
{
p.value = dbnull.value;
}
/*
paranew.parametername = p.parametername;
paranew.sqldbtype = p.sqldbtype;
paranew.dbtype = p.dbtype;
paranew.sourcecolumn = p.sourcecolumn;
paranew.value = p.value;
*/
command.parameters.add(p);
}
}
}
return command;
}
private sqlcommand createcommandhelper(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
{
sqlcommand command = new sqlcommand();
command.commandtext = commandtext;
command.commandtype = commandtype;
command.connection = trans.connection;
command.transaction = trans;
if (paras != null && paras.length > 0)
{
foreach (sqlparameter p in paras)
{
sqlparameter paranew = new sqlparameter();
if (p != null)
{
// check for derived output value with no value assigned
if ((p.direction == parameterdirection.inputoutput ||
p.direction == parameterdirection.input) &&
(p.value == null))
{
p.value = dbnull.value;
}
paranew.parametername = p.parametername;
paranew.sqldbtype = p.sqldbtype;
paranew.dbtype = p.dbtype;
paranew.sourcecolumn = p.sourcecolumn;
paranew.value = p.value;
}
command.parameters.add(paranew);
}
}
return command;
}
#endregion
}
}
上一篇: 扯面和面水和盐的比例是多少?扯面制作上的技巧有哪些?
下一篇: 吃什么补充海绵体,男生要多多注意