<?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
{
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);
}
}
}
/*
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] 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 (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
*/
}
}
应用实例
private DataBase db = new DataBase();
private string Email_Batch = DateTime.Now.ToString("yyyyMMddHHmmss") + Number.GenerateRandom(4);//批号
string sql = "insert into email(email_batch,email_sender,email_to,email_in,email_content,email_date) values(@email_batch,@email_sender,@email_to,@email_in,@email_content,@email_date)";
SqlParameter[] sqlpar = new SqlParameter[6];
sqlpar[0] = new SqlParameter("@email_batch",Email_Batch);
sqlpar[1] = new SqlParameter("@email_sender", this.dataGridView1.Rows[index].Cells[5].Value.ToString());
sqlpar[2] = new SqlParameter("@email_to", this.dataGridView1.Rows[index].Cells[6].Value.ToString());
sqlpar[3]=new SqlParameter("@email_content",this.dataGridView1.Rows[index].Cells[4].Value.ToString());
sqlpar[4] = new SqlParameter("@email_date", Convert.ToDateTime(this.dataGridView1.Rows[index].Cells[2].Value.ToString()));
sqlpar[5] = new SqlParameter("@email_in", FilterEmail(this.dataGridView1.Rows[index].Cells[4].Value.ToString()));
db.ExecuteSql(sql, sqlpar);