//ADO.NET访问sqlserver数据库的命名空间
using System.Data.SqlClient
string constr="server=.database=你需要连接数据库的名称User id=sapwd=你sa账户的密码"
//上述是SQL验证连接数据库的字符串
//如果你的SQL Server是选择Windows验证
//则是这样的
//string connStr = "server=.database=你需要连接数据库的名称integrated security=true"
//实例化一个SqlConnection
SqlConnection conn =new SqlConnection(constr)
conn.Open()
//设置你的SQL语句（如增加、删除、修改）
string sql="insert into table values('属性值1','属性值2','属性值3','属性值4',)"
//新建一个专门执行sql命令的对象
SqlCommand cmd = new SqlCommand()
//执行什么命令
cmd.CommandText = sql
//在哪个连接上执行
cmd.Connection = conn
//上述代码可以简化为：
//SqlCommand cmd = new SqlCommand(sql,conn)
//由于ExecuteNonQuery()返回的是一个int类型的值
//所以定义一个int类型变量i
int i=cmd.ExecuteNonQuery()
if（i>0）
//执行成功
else
//执行失败
//关闭连接，为了安全
conn.Close()
using System.Configurationusing System.Data
using System.Data.SqlClient
using System.Collections
public class DataBase
{
private string ConnStr = null
public DataBase()
{
ConnStr = ConfigurationSettings.AppSettings["ConnStr"]
}
public void RunProc(string SQL)
{
SqlConnection Conn
Conn = new SqlConnection(ConnStr)
Conn.Open()
SqlCommand Cmd
Cmd = CreateCmd(SQL, Conn)
try
{
Cmd.ExecuteNonQuery()
}
catch
{
throw new Exception(SQL)
}
Dispose(Conn)
return
}
}
在另外调用的时候 写
DataBase db = new DataBase ()
string sql = ""//这里是你要执行的SQL语句
db.RunProc(sql)//ok了。
如果是 ASP.NET 把 ConnStr 连接字符串配置在WEB CONFIG 中就可以了。或者直接将
ConnStr = ConfigurationSettings.AppSettings["ConnStr"]改为 ConnStr＝ "Data Source=数据库名User
ID=用户名Password=密码"
ORCALE 牵涉到事务的问题。给你一个底层类。你自己看着调用吧。
using System
using System.Collections
using System.Collections.Specialized
using System.Data
using System.Data.OracleClient
using System.Configuration
namespace gds.DBUtility
{
/// <summary>
/// 数据访问抽象基础类
/// Copyright (C) 2004-2008 LiTianPing
/// All rights reserved
/// </summary>
public abstract class DbHelperSQL
{
//数据库连接字符串(web.config来配置)
public static string connectionString = ConfigurationManager.AppSettings
["gdsconstring"]
public DbHelperSQL()
{
}
#region 公用方法
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName
object obj = DbHelperSQL.GetSingle(strsql)
if (obj == null)
{
return 1
}
else
{
return int.Parse(obj.ToString())
}
}
public static bool Exists(string strSql)
{
object obj = DbHelperSQL.GetSingle(strSql)
int cmdresult
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0
}
else
{
cmdresult = int.Parse(obj.ToString())
}
if (cmdresult == 0)
{
return false
}
else
{
return true
}
}
public static bool Exists(string strSql, params OracleParameter[] cmdParms)
{
object obj = DbHelperSQL.GetSingle(strSql, cmdParms)
int cmdresult
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0
}
else
{
cmdresult = int.Parse(obj.ToString())
}
if (cmdresult == 0)
{
return false
}
else
{
return true
}
}
#endregion
#region 执行简单SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open()
int rows = cmd.ExecuteNonQuery()
return rows
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close()
throw new Exception(E.Message)
}
}
}
}
/// <summary>
/// 执行SQL语句，设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open()
cmd.CommandTimeout = Times
int rows = cmd.ExecuteNonQuery()
return rows
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close()
throw new Exception(E.Message)
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open()
OracleCommand cmd = new OracleCommand()
cmd.Connection = conn
OracleTransaction tx = conn.BeginTransaction()
cmd.Transaction = tx
try
{
for (int n = 0n <SQLStringList.Countn++)
{
string strsql = SQLStringList[n].ToString()
if (strsql.Trim().Length >1)
{
cmd.CommandText = strsql
cmd.ExecuteNonQuery()
}
}
tx.Commit()
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback()
throw new Exception(E.Message)
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open()
object obj = cmd.ExecuteScalar()
if ((Object.Equals(obj, null)) || (Object.Equals(obj,
System.DBNull.Value)))
{
return null
}
else
{
return obj
}
}
catch (System.Data.OracleClient.OracleException e)
{
connection.Close()
throw new Exception(e.Message)
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static OracleDataReader ExecuteReader(string strSQL)
{
OracleConnection connection = new OracleConnection(connectionString)
OracleCommand cmd = new OracleCommand(strSQL, connection)
try
{
connection.Open()
OracleDataReader myReader = cmd.ExecuteReader()
return myReader
}
catch (System.Data.OracleClient.OracleException e)
{
throw new Exception(e.Message)
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose()
// connection.Close()
//}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet()
try
{
connection.Open()
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection)
command.Fill(ds, "ds")
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message)
}
return ds
}
}
/// <summary>
/// 执行查询语句，返回DataSet,设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static DataSet Query(string SQLString, int Times)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet()
try
{
connection.Open()
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection)
command.SelectCommand.CommandTimeout = Times
command.Fill(ds, "ds")
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message)
}
return ds
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms)
int rows = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
return rows
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message)
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的
SqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open()
using (OracleTransaction trans = conn.BeginTransaction())
{
OracleCommand cmd = new OracleCommand()
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString()
OracleParameter[] cmdParms = (OracleParameter[])myDE.Value
PrepareCommand(cmd, conn, trans, cmdText, cmdParms)
int val = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
trans.Commit()
}
}
catch
{
trans.Rollback()
throw
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms)
object obj = cmd.ExecuteScalar()
cmd.Parameters.Clear()
if ((Object.Equals(obj, null)) || (Object.Equals(obj,
System.DBNull.Value)))
{
return null
}
else
{
return obj
}
}
catch (System.Data.OracleClient.OracleException e)
{
throw new Exception(e.Message)
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static OracleDataReader ExecuteReader(string SQLString, params OracleParameter[]
cmdParms)
{
OracleConnection connection = new OracleConnection(connectionString)
OracleCommand cmd = new OracleCommand()
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms)
OracleDataReader myReader = cmd.ExecuteReader()
cmd.Parameters.Clear()
return myReader
}
catch (System.Data.OracleClient.OracleException e)
{
throw new Exception(e.Message)
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose()
// connection.Close()
//}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand()
PrepareCommand(cmd, connection, null, SQLString, cmdParms)
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet()
try
{
da.Fill(ds, "ds")
cmd.Parameters.Clear()
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message)
}
return ds
}
}
}
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn,
OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open()
cmd.Connection = conn
cmd.CommandText = cmdText
if (trans != null)
cmd.Transaction = trans
cmd.CommandType = CommandType.Text//cmdType
if (cmdParms != null)
{
foreach (OracleParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput ||
parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value
}
cmd.Parameters.Add(parameter)
}
}
}
#endregion
}
}
//查string sel="select * from tab"
conn.Open()
SqlCommand cmd=new SqlCommand(sel,conn)
SqlDataReader dr=cmd.ExecuteReader()
if(dr.HashRow)
{
while(dr.Read())
{
labe1.Text=dr[0]
......
}
}
conn.close()
//增，删，改都是这个写法就是SQL语句不同而已
string insert="insert into tab (...) values(.....)"
conn.Open()
SqlCommand cmd=new SqlCommand(insert,conn)
cmd.ExecuteNonQuery()
conn.close()
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）