//获取行数 internal int GetRows(string select, object[] objParam) { int iRst = 0; DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { SqlCommand cmd = new SqlCommand(select, cn.sqlCon); for (int i = 0; i < objParam.Length; i++) { cmd.Parameters.Add( new SqlParameter("@Param" + i.ToString(), objParam[i])); }; cn.sqlCon.Open(); object oRst = cmd.ExecuteScalar(); cn.sqlCon.Close(); if (oRst != null) { iRst = Int32.Parse(oRst.ToString()); } }; return iRst; }
//调取存储过程获取数据集 internal DataTable GetDataBySP (string SPName, object[] objParamName, object[] objParamValue) { DataTable dt = new DataTable(); DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { SqlCommand cmd = new SqlCommand(SPName, cn.sqlCon); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = SPName; if (objParamName.Length == objParamValue.Length) { for (int i = 0; i < objParamName.Length; i++) { cmd.Parameters.Add(new SqlParameter( "@" + objParamName[i], objParamValue[i])); }; SqlDataAdapter da = new SqlDataAdapter(cmd); cn.sqlCon.Open(); da.Fill(dt); cn.sqlCon.Close(); } else { dt = null; }; }; return dt; }
//获取数据集 internal DataTable GetData (string select, object[] objParam) { DataTable dtRst = new DataTable(); DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { SqlCommand cmd = new SqlCommand(select, cn.sqlCon); for (int i = 0; i < objParam.Length; i++) { cmd.Parameters.Add( new SqlParameter("@Param" + i.ToString(), objParam[i])); }; SqlDataAdapter da = new SqlDataAdapter(cmd); cn.sqlCon.Open(); da.Fill(dtRst); cn.sqlCon.Close(); }; return dtRst; }
/// <summary> /// 多表操作用该方法 /// </summary> /// <param name="pkColumn">Update时的主键,请在数据库表中设置相应的主键</param> /// <param name="dsUpdate">需要操作的数据表集合</param> /// <returns></returns> internal bool UpdateDB (string pkColumn, DataSet dsUpdate) { bool bRst = false; int haveDt = dsUpdate.Tables.Count; DataTable dtSoucre = new DataTable(); DataTable dtSubmit = new DataTable(); if (haveDt > 0) { DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { cn.sqlCon.Open(); SqlTransaction sqlTransaction = cn.sqlCon.BeginTransaction(); try { //处理表情况 for (int i = 0; i < dsUpdate.Tables.Count; i++) { string tabName = dsUpdate.Tables[i].TableName; string RowStatus = dsUpdate.Tables[i].Rows[0].RowState.ToString(); string select = "SELECT TOP 0 * FROM "; select += tabName + " WHIT(NOLOCK)"; DataTable dt = dsUpdate.Tables[i].Copy(); if (RowStatus == "Modified") { string soucre = @"SELECT * FROM " + tabName + " WITH(NOLOCK) "; dtSoucre = GetData(soucre, new object[0]); for (int j = 0; j < dt.Rows.Count; j++) { string updatePK = dt.Rows[j][pkColumn].ToString(); for (int k = 0; k < dtSoucre.Rows.Count; k++) { string sourePK = dtSoucre.Rows[k][pkColumn].ToString(); if (updatePK == sourePK) { for (int l = 0; l < dt.Columns.Count; l++) { string ColName = dt.Columns[l].ColumnName; dtSoucre.Rows[k][ColName] = dt.Rows[j][ColName]; }; }; }; }; }; SqlCommand cmd = new SqlCommand(select, cn.sqlCon); cmd.Transaction = sqlTransaction; SqlDataAdapter adapter = new SqlDataAdapter(cmd); if (RowStatus == "Added") { adapter.Fill(dt); dtSubmit = dt.Copy(); } else if (RowStatus == "Modified") { adapter.Fill(dtSubmit); dtSubmit = dtSoucre.GetChanges(); //设置主键 ManufacturePrimaryKey(pkColumn, dtSubmit); }; SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.Update(dtSubmit); }; sqlTransaction.Commit(); cn.sqlCon.Close(); bRst = true; } catch (Exception) { bRst = false; sqlTransaction.Rollback(); cn.sqlCon.Close(); }; }; }; return bRst; }
/// <summary> /// 单表操作,速度快,占用稀缺资源少 /// </summary> /// <param name="tabName">更新的表名</param> /// <param name="pkColumn">字段</param> /// <param name="dtUpdate">数据</param> /// <returns></returns> internal bool Update (string tabName, string pkColumn, DataTable dtUpdate) { bool bRst = false; DataTable dtSoucre = new DataTable(); DataTable dtSubmit = new DataTable(); string select = @"SELECT * FROM " + tabName + " WITH(NOLOCK) "; dtSoucre = GetData(select, new object[0]); for (int i = 0; i < dtUpdate.Rows.Count; i++) { string updatePK = dtUpdate.Rows[i][pkColumn].ToString(); for (int j = 0; j < dtSoucre.Rows.Count; j++) { string sourePK = dtSoucre.Rows[j][pkColumn].ToString(); if (updatePK == sourePK) { for (int k = 0; k < dtUpdate.Columns.Count; k++) { string ColName = dtUpdate.Columns[k].ColumnName; dtSoucre.Rows[j][ColName] = dtUpdate.Rows[i][ColName]; }; }; }; }; DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { string query = @"SELECT TOP 0 * FROM " + tabName + "WITH(NOLOCK) "; cn.sqlCon.Open(); SqlCommand cmd = new SqlCommand(query, cn.sqlCon); SqlTransaction sqlTransaction = cn.sqlCon.BeginTransaction(); cmd.Transaction = sqlTransaction; SqlDataAdapter adapter = new SqlDataAdapter(cmd); try { //获取表架构 adapter.Fill(dtSubmit); ////将更新的值插入表 dtSubmit = dtSoucre.GetChanges(); //设置主键 ManufacturePrimaryKey(pkColumn, dtSubmit); //自动处理行状态 SqlCommandBuilder builder = new SqlCommandBuilder(adapter); //将数据写入数据库 adapter.Update(dtSubmit); sqlTransaction.Commit(); cn.sqlCon.Close(); bRst = true; } catch (Exception e) { bRst = false; sqlTransaction.Rollback(); cn.sqlCon.Close(); } }; return bRst; }
/// <summary> /// 单表新增数据 /// </summary> /// <param name="tabName">目标表名</param> /// <param name="dt">数据</param> /// <returns></returns> internal bool Insert(string tabName, DataTable dt) { bool bRst = false; string select = @"SELECT TOP 0 * FROM " + tabName + " WHIT(NOLOCK)"; DataTable dtSubmit = new DataTable(); DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { cn.sqlCon.Open(); SqlCommand cmd = new SqlCommand(select, cn.sqlCon); SqlTransaction sqlTransaction = cn.sqlCon.BeginTransaction(); cmd.Transaction = sqlTransaction; try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dt); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.Update(dt); sqlTransaction.Commit(); cn.sqlCon.Close(); bRst = true; } catch (Exception) { sqlTransaction.Rollback(); cn.sqlCon.Close(); throw; } }; return bRst; }
//获取表头 internal DataTable GetTabHead(string tabName) { string select = @"SELECT TOP 0 * FROM " + tabName + " WHIT(NOLOCK)"; DataTable dt = new DataTable(); DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { SqlCommand cmd = new SqlCommand(select, cn.sqlCon); SqlDataAdapter adapter = new SqlDataAdapter(cmd); cn.sqlCon.Open(); adapter.Fill(dt); cn.sqlCon.Close(); return dt; } }