因为需要操作SqlServer数据库中的数据
变量定义
/// <summary>
/// IP
/// </summary>
public string gstrIP;
/// <summary>
/// 密码
/// </summary>
public string gstrPwd;
/// <summary>
/// 用户名
/// </summary>
public string gstrUser;
/// <summary>
/// 数据库
/// </summary>
public string gstrDataLib;
//错误信息
private string l_strErrMsg = "";
c#链接数据库
public bool OpenCon()
{
string strSqlConstring;//连接字符串
strSqlConstring = "Password=" + gstrPwd + ";";
strSqlConstring += "User ID=" + gstrUser + ";Data Source=" + gstrIP + ";";
strSqlConstring += "Persist Security Info=True;Initial Catalog=" + gstrDataLib + "";
try
{
if (l_sqlCon.State == ConnectionState.Open)
{
l_sqlCon.Close();
l_sqlCon.ConnectionString = strSqlConstring;
l_sqlCon.Open();
return true;
}
else
{
l_sqlCon.ConnectionString = strSqlConstring;
l_sqlCon.Open();
return true;
}
}
catch (Exception e)
{
l_strErrMsg = e.Message;
return false;
}
}
关闭数据库
/// <summary>
/// 功能描述：关闭sql数据库
/// </summary>
public void CloseCon()
{
l_sqlCon.Close();
l_sqlCon.Dispose();
}
执行SQL语句
根据需要返回值不同
/// <summary>
/// 功能：执行SQL语句
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataTable ExecuteSQLDT(string strSQL)
{
DataTable sqldt = new DataTable();
try
{
if (l_sqlCon.State != ConnectionState.Open) { l_sqlCon.Open(); }
SqlDataAdapter SDA = new SqlDataAdapter(strSQL, l_sqlCon);
SDA.Fill(sqldt);
return sqldt;
}
catch (Exception e)
{
sqldt.Clear();
//CloseCon();
l_strErrMsg = e.ToString();
return sqldt;
}
}
/// <summary>
/// 执行sql语句返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public SqlDataReader ExecuteSQLReader(string strSQL)
{
SqlDataReader dr = null;
try
{
if (l_sqlCon.State != ConnectionState.Open) { l_sqlCon.Open(); }
SqlCommand sqlcommand = new SqlCommand(strSQL, l_sqlCon);
dr = sqlcommand.ExecuteReader();
return dr;
}
catch (Exception e)
{
dr = null;
//CloseCon();
l_strErrMsg = e.ToString();
return dr;
}
}
/// <summary>
/// 执行sql不返回受影响行数
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public bool ExecuteSQL(string strSQL)
{
int iFluencedLine = 0;
try
{
if (l_sqlCon.State != ConnectionState.Open) { l_sqlCon.Open(); }
SqlCommand sqlcommand = new SqlCommand(strSQL, l_sqlCon);
iFluencedLine = sqlcommand.ExecuteNonQuery();
return true;
}
catch (Exception e)
{
//CloseCon();
l_strErrMsg = e.Message;
return false;
}
}
查询一张表
/// <summary>
/// 根据外部传入的SQL条件语句返回表
/// </summary>
/// <param name="strTable"></param>
/// <param name="strAddedSql"></param>
/// <returns></returns>
public DataTable DTSelectSQLDisp(string strTable, string strAddedSql)
{
string strsql;
DataTable dt;
if (strAddedSql == "")
{
strsql = "select ";
for (int i = 0; i < l_LTselectCol.Count; i++)
{
if (i != l_LTselectCol.Count - 1)
strsql += l_LTselectCol[i] + ",";
else
strsql += l_LTselectCol[i];
}
strsql += " from [" + strTable + "] ";
}
else
{
strsql = "select ";
for (int i = 0; i < l_LTselectCol.Count; i++)
{
if (i != l_LTselectCol.Count - 1)
strsql += l_LTselectCol[i] + ",";
else
strsql += l_LTselectCol[i];
}
strsql += " from [" + strTable + "] where 1=1" + strAddedSql;
}
dt = ExecuteSQLDT(strsql);
return dt;
}
带不同返回值
/// <summary>
/// 查询数据库,可添加条件语句
/// </summary>
/// <param name="strTable"></param>
/// <returns></returns>
public SqlDataReader DTSelectSQLData(string strTable, string strAddedSql)
{
string strsql;
DataTable DT = new DataTable();
DataTable DT2 = new DataTable();
SqlDataReader DR = null;
if (strAddedSql == "")
{ strsql = "select * from [" + strTable + "] "; }
else
{ strsql = "select * from [" + strTable + "] where 1=1" + strAddedSql; }
l_strsql = strsql;
DR = ExecuteSQLReader(strsql);
return DR;
}
带排序查表
public DataTable DTselectTable(string strTable, string strOrder)
{
string strsql = "select * from [" + strTable + "] order by [" + strOrder + "] desc";
DataTable dt = new DataTable();
dt = ExecuteSQLDT(strsql);
return dt;
}
创建表
string[] strFieldArr = new string[] { "ID", "Station", "Barcode", "DT", "Code", "Describe", "[User]" };
string[] strDataTypeArr = new string[] { "bigint identity (1,1) primary key", "nvarchar(50)", "nvarchar(50) not null", "datetime", "nvarchar(50)", "nvarchar(255)", "nvarchar(50)" };
/// <summary>
/// 创建表
/// </summary>
/// <param name="strDataLib"></param>
/// <param name="strTableName"></param>
/// <param name="DicFieldData">dictionary<string,string>字典列名数据集合</param>
/// <returns></returns>
public bool CreateSqlRepTable(string strDataLib, string strTableName)
{
string strSQL = "USE " + strDataLib + " create table [" + strTableName + "] (";
foreach (var item in l_DicRepairingField)
{
strSQL += item.Key + " " + item.Value + ",";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
if (HasSqlTable(strTableName) == true)
{
if (HasSqlSameColName(strTableName, l_DicRepairingField) == true)
{
return true;
}
else
{
l_strErrMsg = "创建数据库表格错误.已存在字段不同的表格";
return false;
}
}
if (ExecuteSQL(strSQL) == false)
{
l_strErrMsg = "创建数据库表格错误." + l_strErrMsg;
return false;
}
return true;
}
参考：C#获得Sqlserver数据库中所有表名
C#新建SQL数据表
检查某数据库内是否有相同表
/// <summary>
/// 功能描述：检查数据库内是否有相同的表
/// </summary>
/// <param name="strTableName">检查的表名</param>
/// <returns></returns>
public bool HasSqlTable(string strTableName)
{
//从连接中获得所有表
DataTable dt = l_sqlCon.GetSchema("Tables");
//用于存放表格的列表
List<string> tableNameList = new List<string>();
foreach (DataRow row in dt.Rows)
{
//得到表名
string tablename = (string)row[2];
//如果直接想获得这个数据库下的所有表，可以直接添加；
tableNameList.Add(tablename);
//同样可以添加条件，对需要的表格进行筛选
//if (tablename.Contains("xxx"))
// tableNameList.Add(tablename);
}
//遍历集合，是否有相同的表名
for (int i = 0; i < tableNameList.Count; i++)
{
if (strTableName == tableNameList[i])
{
return true;
}
}
return false;
}
检查某表内是否有相同的字段和限制
参考：SQL Server获取表结构信息(字段名、类型、长度、精度、小数位数、主键、自动增长).
C#获取SqlServer表字段信息
C# 如何获取SQL Server 中指定数据表的所有字段名和字段类型
MSDN文档
MSDN Sqlserver架构集合
/// <summary>
/// 检查是否有相同的字段名和字段类型限制值
/// </summary>
/// <param name="strTableName">查询的表名</param>
/// <param name="DicFieldADataType"></param>
/// <returns></returns>
public bool HasSqlSameColName(string strTableName, Dictionary<string, string> DicFieldADataType)
{
//从连接中获得所有表
DataTable dt = l_sqlCon.GetSchema("Columns", new string[] { null, null, strTableName, null });
Dictionary<string, string> DicCol = new Dictionary<string, string>();
//检查表格数据
foreach (DataRow dr in dt.Rows)
{
string Name = dr["column_name"].ToString();
string Type = dr["data_type"].ToString();
try
{
DicCol.Add(Name, Type);
}
catch (Exception)
{ }
}
foreach (var item1 in DicFieldADataType)
{
bool bolRes = false;
foreach (var item2 in DicCol)
{
if (item2.Key == item1.Key || String.Join("", item1.Key.Split(new string[] { "[", "]" }, StringSplitOptions.RemoveEmptyEntries)) == item2.Key)//字段名相同
{
if (item2.Value == item1.Value)//字段类型相同
{
bolRes = true;
}
else if (item1.Value.ToLower().Contains("nvarchar") && item2.Value.ToLower().Contains("nvarchar"))
{ bolRes = true; }
else if (item1.Value.Contains(item2.Value))
{ bolRes = true; }
}
}
if (bolRes == false)
{
return false;
}
}
return true;
}
删除行
public bool DeleteTableData(string strTableName,string strBar)
{
string strSql = "delete from [" + strTableName + "] where Bar='" + strBar + "'";
if (ExecuteSQL(strSql) == true)
{
return true;
}
else
{
l_strErrMsg = "删除数据库数据错误!" + l_strErrMsg;
return false;
}
}
1092
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
