c#操作mssql数据库含有带参数sql语句运⾏
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="Default" value="Default" />
</appSettings>
<connectionStrings>
<add name="Default" connectionString="Data Source=192.168.1.197;User ID=diamond;Password=88888888;Initial Catalog=EmailAnalyse" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
namespace OfficeOutlook
{
/// <summary>
///DataBase 的摘要说明
/
// </summary>
public class DataBase
{
protected SqlConnection BaseSqlConnection = new SqlConnection();//连接对象
protected SqlCommand BaseSqlCommand = new SqlCommand();  //命令对象
public DataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
protected void OpenConnection()
{
if (BaseSqlConnection.State == ConnectionState.Closed)  //连接是否关闭
try
{
BaseSqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["Default"].ToString();                    BaseSqlCommand.Connection = BaseSqlConnection;
BaseSqlConnection.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void CloseConnection()
{
if (BaseSqlConnection.State == ConnectionState.Open)
{
BaseSqlConnection.Close();
BaseSqlConnection.Dispose();
BaseSqlCommand.Dispose();
}
}
public bool BaseExecuteNonQuery(String SqlString)    //可以执⾏ 插⼊、删除、查询        {
try
{
OpenConnection();
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
BaseSqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return true;
}
public int BaseExecuteNonQueryCount(String SqlString)    //影响的⾏数
{
int count = 0;
try
{
OpenConnection();
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
count = BaseSqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return count;
}
public string FirstColumn(String SqlString)  //返回⾸⾏⾸列的值
{
string val = null;
try
{
OpenConnection();
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
val = BaseSqlCommand.ExecuteScalar().ToString();
BaseSqlCommand.Parameters.Clear();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return val;
}
public object BaseExecuteScalar(String SqlString)  //返回⾸⾏⾸列        {
object BaseObject = new object();
try
{
OpenConnection();
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
BaseObject = BaseSqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return true;
}
public SqlDataReader BaseExecuteReader(String SqlString)
{
SqlDataReader BaseDataReader = null;
try
{
OpenConnection();  //打开连接
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
BaseDataReader = BaseSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);            }
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//finally
//{
//    CloseConnection();
//}
return BaseDataReader;
}
public DataSet BaseDataSet(String SqlString, string DataSetName)
{
DataSet BaseDataSet = new DataSet();
SqlDataAdapter BaseDataAdapter = new SqlDataAdapter();
try
sqltransaction什么意思
{
OpenConnection();
BaseSqlCommand.CommandType = CommandType.Text;
BaseSqlCommand.CommandText = SqlString;
BaseDataAdapter.SelectCommand = BaseSqlCommand;
BaseDataAdapter.Fill(BaseDataSet, DataSetName);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection();
}
return BaseDataSet;
}
/// <summary>
/
// 执⾏SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public  int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
try
{
PrepareCommand(null, SQLString, cmdParms);
int rows = BaseSqlCommand.ExecuteNonQuery();
BaseSqlCommand.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
//  ITNB.Base.Error.showError(E.Message.ToString());
}
finally
{
CloseConnection();
}
}
private  void PrepareCommand(SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)        {
OpenConnection();
BaseSqlCommand.Connection = BaseSqlConnection;
BaseSqlCommand.CommandText = cmdText;
if (trans != null)
{
BaseSqlCommand.Transaction = trans;
}
BaseSqlCommand.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
BaseSqlCommand.Parameters.Add(parameter);
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。