c#连接sqlserver数据库、插入数据、从数据库获取时间示例
c#连接sqlserver、插入数据、从数据库获取时间
using system;
using system.data.sqlclient;
namespace test
{
//连接数据库
public class connection
{
private static string connectionstring =
"server = 192.168.1.222;" +
"database = test;" +
"user id = test;" +
"password = abc123;";
/// <summary>
/// 连接数据库
/// </summary>
/// <returns></returns>
private sqlconnection connectionopen()
{
sqlconnection conn = new sqlconnection(connectionstring);
conn.open();
return conn;
}
/// <summary>
/// 向表(table)中插入一条数据
/// </summary>
public void insert(string value1, string value2, string value3, datetime datetime)
{
sqlconnection conn = connectionopen();
string sql =
"insert into table(row1, row2, row3, datetime) values ('" +
value1 + "', '" + value2 + "', '" + value3 + "', '" + datetime + "')";
sqlcommand comm = new sqlcommand(sql, conn);
comm.executereader();
conn.close();
}
/// <summary>
/// 从数据库中获取当前时间
/// </summary>
/// <returns></returns>
public datetime getdatetimefromsql()
{
sqlconnection conn = connectionopen();
string sql = "select getdate()";
sqlcommand comm = new sqlcommand(sql, conn);
sqldatareader reader = comm.executereader();
datetime dt;
if (reader.read())
{
dt = (datetime)reader[0];
conn.close();
return dt;
}
conn.close();
return datetime.minvalue;
}
}
}
c#连接sql server 2008示例
/*
* 说明
* 功能说明:数据访问封装。所有数据都要通过这个类定义的dbconnection访问数据库。
* 同时，定义了通用的cmd，以及cmd常用的访问存储过程的方法runpro
*
* 作者: rogerwang
*
* 创建日期:2006-02-15
*
*/
using system;
using system.data;
using system.data.sqlclient;
namespace insurer
{
/// <summary>
/// dataaccess 的摘要说明。
/// </summary>
public class dataaccess
{
private readonly string sqlconnectstr = "server=(local);uid=sa;pwd=lwrong;database=insurer";
private sqlconnection dbconnection;
private readonly string retuenvalue = "returnvalue";
//判断要不要启动事务
private bool startrans = false;
//为解决多笔数据导入的问题,特添加的事务处理属性
private sqltransaction trans = null;
//定义是否启动事务属性
public bool starttrans
{
get
{
return startrans;
}
set
{
startrans = value;
}
}
//定义事务
public sqltransaction trans
{
get
{
return trans;
}
set
{
if (value != null)
{
trans = value;
}
}
}
//创建打开dbconnection对象
public void openconnection()
{
if ( dbconnection == null )
{
dbconnection = new sqlconnection(sqlconnectstr);
}
if ( dbconnection.state == connectionstate.closed )
{
try
{
dbconnection.open();
}
catch(exception ex)
{
systemerror.systemlog(ex.message);
}
finally
{
}
}
}
//释放dbconnection对象
public void closeconnection()
{
if (dbconnection != null)
{
if (dbconnection.state == connectionstate.open)
{
dbconnection.dispose();
dbconnection = null;
}
}
}
//
//创建cmd,注意dbconnection在该函数中创建，但没有在这函数中释放。
//在正确的面向对象设计方法中，对象应该是谁创建，谁就应该负责释放。按这个观点，这个过程有些不安全!!!!
private sqlcommand createcommand(string proname,sqlparameter[] prams)
{
openconnection();
sqlcommand cmd = new sqlcommand(proname,dbconnection);
cmd.commandtype = commandtype.storedprocedure;
//如果进行事务处理,那么对cmd的transaction的事务赋值
if (starttrans)
{
cmd.transaction = trans;
}
if ( prams != null)
{
foreach(sqlparameter parameter in prams)
{
cmd.parameters.add(parameter);
}
}
//cmd.parameters.add(
return cmd;
}
/// <summary>
/// 创建cmd,并执行相应的操作。 然后释放cmd!
///
/// 该函数是执行cmd没有返回值，且没有参数的方法。
/// </summary>
/// <param name="proname"></param>
public bool runproc(string proname)
{
sqlcommand cmd = createcommand(proname,null);
bool k = false;
try
{
k = (bool)cmd.executescalar();
}
catch(exception ex)
{
systemerror.systemlog(ex.message);
}
finally
{
cmd.dispose();
}
return k;
}
/// <summary>
/// 创建cmd,并执行相应的操作。 然后释放cmd!
///
/// 该函数是执行cmd没有返回值,但有参数的方法。
/// </summary>
/// <param name="proname"></param>
/// <param name="prams"></param>
public bool runproc(string proname,sqlparameter[] prams)
{
sqlcommand cmd = createcommand(proname,prams);
bool k = false;
try
{
k = (bool) cmd.executescalar();
}
catch(exception ex)
{
systemerror.systemlog(ex.message);
}
finally
{
cmd.dispose();
//close();
}
return k;
}
/// <summary>
/// 创建cmd,并执行相应的操作。 然后释放cmd!
///
/// 该函数是执行cmd带有返回值,但没有参数的方法。
/// </summary>
/// <param name="proname"></param>
/// <param name="datareader"></param>
public void runproc(string proname,out sqldatareader datareader)
{
sqlcommand cmd = createcommand(proname,null);
datareader = cmd.executereader(commandbehavior.closeconnection);
try
{
}
catch(exception ex)
{
systemerror.systemlog(ex.message);
}
finally
{
cmd.dispose();
}
}
/// <summary>
/// 创建cmd,并执行相应的操作。 然后释放cmd!
///
/// 该函数是执行cmd带有返回值,且有参数的方法。
/// </summary>
/// <param name="proname"></param>
/// <param name="prams"></param>
/// <param name="datareader"></param>
public void runproc(string proname,sqlparameter[] prams,out sqldatareader datareader)
{
sqlcommand cmd = createcommand(proname,prams);
datareader = cmd.executereader(commandbehavior.closeconnection);
try
{
}
catch(exception ex)
{
systemerror.systemlog(ex.message);
}
finally
{
cmd.dispose();
}
}
/// <summary>
/// 创建cmd的参数
/// 该方法的思路就是按条件生成一个sqlparameter对象。
/// 生成对象后，再给对象赋相应的返回值类型
/// </summary>
/// <param name="paramname"></param>
/// <param name="dbtype"></param>
/// <param name="size"></param>
/// <param name="direction"></param>
/// <param name="value"></param>
/// <returns></returns>
public sqlparameter createparam(string paramname, sqldbtype dbtype, int size,parameterdirection direction,object value)
{
sqlparameter param;
if (size > 0)
{
param = new sqlparameter(paramname,dbtype,size);
}
else
{
param = new sqlparameter(paramname,dbtype);
}
param.direction = direction;
param.value = value;
return param;
}
/// <summary>
/// 创建cmd的输入参数
/// </summary>
/// <param name="paramname"></param>
/// <param name="dbtype"></param>
/// <param name="size"></param>
/// <param name="value"></param>
/// <returns></returns>
public sqlparameter createinparam(string paramname, sqldbtype dbtype, int size, object value)
{
return createparam(paramname,dbtype,size,parameterdirection.input,value);
}
/// <summary>
/// 创建cmd的输出参数
/// </summary>
/// <param name="paramname"></param>
/// <param name="dbtype"></param>
/// <param name="size"></param>
/// <returns></returns>
public sqlparameter createoutparam(string paramname, sqldbtype dbtype, int size)
{
return createparam(paramname,dbtype,size,parameterdirection.output,null);
}
/// <summary>
/// 创建cmd带有返回值的参数
/// </summary>
/// <param name="paramname"></param>
/// <param name="dbtype"></param>
/// <param name="size"></param>
/// <returns></returns>
public sqlparameter createreturnparam(string paramname,sqldbtype dbtype, int size)
{
return createparam(paramname,dbtype,size,parameterdirection.returnvalue,null);
}
//开始一个事务
public void begintrans()
{
openconnection();
trans = dbconnection.begintransaction(isolationlevel.serializable);
}
public void commit()
{
if (trans != null)
{
trans.commit();
}
}
public void rollback()
{
if (trans != null)
{
trans.rollback();
}
}
}
}