using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
class DbHelp
{
public static string ConnectionString = "";
public static string Error = "";
/// <summary>
/// 更新一个由客户端传回的记录集
/// </summary>
/// <param name="DataSetWithSQL"></param>
/// <param name="SQLString"></param>
/// <returns></returns>
public bool UpdateDataSetBySQL(DataSet DataSetWithSQL, string SQLString)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.ConnectionString = ConnectionString;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(SQLString, conn);
SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(da);
da.Update(DataSetWithSQL.Tables[0]);
conn.Close();
return true;
}
}
catch (Exception ex)
{
Error = ex.ToString();
return false;
}
}
//更新或者写入数据库
public int UpdateBySQL(string sql)
{
#region
using (SqlConnection con = new SqlConnection(ConnectionString))
{
con.Open();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand(sql, con);
object objResult = cmd.ExecuteScalar();
con.Close();
if (objResult == null)
{
return 0;
}
else
{
// string result = objResult.ToString();
return 1;
}
}
catch (Exception ex)
{
Error = ex.ToString();
con.Close();
return -1;
}
}
#endregion
}
/// <summary>
/// 通用执行存储过程程序.
/// </summary>
/// <param name="SQLCmd">传入的SqlCommand对象</param>
/// <param name="ReturnDataSet">执行存储过程后返回的数据集</param>
/// <param name="ReturnValue">执行存储过程的返回值</param>
/// <returns>将SQLCmd执行后的参数刷新并传回,主要返回存储过程中的out参数</returns>
public SqlCommand RunStoredProcedure(SqlCommand SQLCmd, out DataSet ReturnDataSet, out int ReturnValue)
{
ReturnValue = 0;
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
SQLCmd.Connection = conn;
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLCmd.CommandTimeout = conn.ConnectionTimeout;
SQLCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
SQLCmd.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = SQLCmd;
DataSet ds = new DataSet("WCFSQLDataSet");
adapter.Fill(ds, "WCFSQLDataSet");
ReturnDataSet = ds;
conn.Close();
ReturnValue = (int)SQLCmd.Parameters["RETURN_VALUE"].Value;
//ReturnValue = int.Parse(SQLCmd.Parameters["RETURN_VALUE"].Value.ToString());
return SQLCmd;
}
}
catch (Exception ex)
{
Error = ex.ToString();
ReturnDataSet = null;
return null;
}
}
/// <summary>
/// 执行一个指定的SQL字串,并返回一个记录集
/// 在浏览器下执行时,直接调用浏览器的WCF服务器来传送记录集
/// </summary>
/// <param name="SQLString">SQL字串</param>
/// <returns>返回的记录集</returns>
public DataSet GetDataSetBySQLString(string SQLString)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = SQLString;
comm.CommandType = CommandType.Text;
comm.CommandTimeout = conn.ConnectionTimeout;
DataSet ds = new DataSet("SQLDataSet");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds, "SQLDataSet");
conn.Close();
return ds;
}
}
public DataTable GetDataTableBySql(string sql)
{
try
{
return GetDataSetBySQLString(sql).Tables[0];
}
catch (Exception)
{
return null;
}
}
}
调用方法:
private void Form1_Load(object sender, EventArgs e)
{
DbHelp.ConnectionString ="server=.;database=TJ_ZhuYou_SPC;uid=sa;pwd=sa;Connect Timeout=5";
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
CreateLot("11");
}
private bool CreateLot(string flag)
{
string ParaName = "";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "存储过程名";
//存储过程的参数
ParaName = "@flag";
cmd.Parameters.Add(new SqlParameter(ParaName, SqlDbType.Char, 1));
cmd.Parameters[ParaName].Direction = ParameterDirection.Input;
cmd.Parameters[ParaName].Value = flag;
ParaName = "@MSG";
cmd.Parameters.Add(new SqlParameter(ParaName, SqlDbType.NVarChar, 100));
cmd.Parameters[ParaName].Direction = ParameterDirection.Output;
int result = -1;
DataSet ds = new DataSet();
DbHelp dh = new DbHelp();
cmd = dh.RunStoredProcedure(cmd, out ds, out result);
if (DbHelp.Error != "")
{
MessageBox.Show(DbHelp.Error);
DbHelp.Error = "";
return false;
}
string ReturnMessage = cmd.Parameters["@MSG"].Value.ToString();
if (result == 0)
{
return true;
}
else
{
MessageBox.Show(this, ReturnMessage, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return false;
}
}
调用sql语句 查询
DataTable dt = new DataTable();
DataAccess da = new DataAccess();
da.QueryString = "SELECT LineName,ColorA,ColorB,ColorG,ColorR FROM dbo.Line_Config ";
dt = da.ExecuteDataTable();
da.Close();
dt.Dispose();
更新:
DataTable dt = new DataTable();
DataAccess da = new DataAccess();
da.QueryString = "update ................'";
da.ExecuteCmd();
da.Close();
对新手比较友好.