一个Sql-server数据库连接,只适合初学者哦
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
/// <summary>
/// 操作数据库公共类
/// </summary>
class SqlDataAccess : IDisposable
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private readonly string SqlConnectionString = "连接字符串;";
private SqlConnection sqlConnection;
public SqlDataAccess()
{
sqlConnection = new SqlConnection(SqlConnectionString);
}
/// <summary>
/// 执行SQL语句,并返回数据库受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public int ExcuteNonQuery(string sql)
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
int result = cmd.ExecuteNonQuery();
sqlConnection.Close();
return result;
}
//如果更新的数据有特殊字符的用上面那个ExcuteNonQuery()就不行了,需要用到SqlCommand 的一个参数SqlParameter
public int ExcuteNonQuery(string sql,SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
cmd.Parameters.AddRange(parameters);
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
int count = cmd.ExecuteNonQuery();
sqlConnection.Close();
return count;
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
object result = cmd.ExecuteScalar();
sqlConnection.Close();
return result;
}
/// <summary>
/// 获取一个DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql)
{
SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
}
}
示例:用上面的数据库连接类,新增一个商品,包括商品名称和单价
string insertSql = @"INSERT INTO PRODUCTINFO
VALUES
(
@PRODUCTNAME,
@PRODUCTPRICE,
);
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@PRODUCTNAME",商品名称){SqlDbType=SqlDbType.NVarChar},
new SqlParameter("@PRODUCTPRICE",商品单价){SqlDbType=SqlDbType.Money}
}
DataAccess dataAccess = new DataAccess();
int result = dataAccess.ExcuteNonQuery(insertSql, parameters);
if (result > 0)
{
MessageBox.Show("新增成功");
}
else
{
MessageBox.Show("新增失败");
}