/// <summary> /// 执行Sql命令,调用SqlTransaction事务,返回bool值 /// </summary> /// <param name="m_cmds">可变的SqlCommand数组,必须指定CommandText和CommandType</param> /// <returns>true或false</returns> public bool ExecNoQueryList(SqlCommand[] m_cmds) { SqlConnection conn = new SqlConnection(SqlConn.GetSqlConnStr()); conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { foreach (SqlCommand cmd in m_cmds) { cmd.Connection = conn; cmd.Transaction = trans; cmd.ExecuteNonQuery(); } trans.Commit(); return(true); } catch (InvalidOperationException ept) { trans.Rollback(); //throw new DBOpenException(ept.Message,ept); return(false); } catch (SqlException ept) { //WriteEventLog(ept.Message); trans.Rollback(); //throw new DBQueryException(ept.Message,ept); return(false); } catch (Exception ept) { ////Console.WriteLine(ept.Message); trans.Rollback(); //throw ept; return(false); } finally { // 确保数据库连接关闭 if (conn.State != ConnectionState.Closed) { conn.Close(); } } } }
/// <summary> /// 返回查询的XML /// </summary> /// <param name="rootName">定义XML根节点名称</param> /// <returns></returns> public XmlDocument ExecXmlDom(string rootName) { string strXml = ""; SqlConnection conn = new SqlConnection(SqlConn.GetSqlConnStr()); m_cmd.Connection = conn; try { conn.Open(); XmlReader read = m_cmd.ExecuteXmlReader(); while (read.ReadState != ReadState.EndOfFile) { read.MoveToContent(); strXml += read.ReadOuterXml(); } } catch (InvalidOperationException ept) { throw new DBOpenException(ept.Message, ept); } catch (SqlException ept) { //WriteEventLog(ept.Message); throw new DBQueryException(ept.Message, ept); } catch (Exception ept) { //Console.WriteLine(ept.Message); throw ept; } finally { // 确保数据库连接关闭 if (conn.State != ConnectionState.Closed) { conn.Close(); } } XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml("<" + rootName + ">" + strXml + "</" + rootName + ">"); return(xmlDoc); }
/// <summary> /// 获取查询结集合 /// </summary> /// <param name="tableName">标注表名</param> /// <returns></returns> public DataTable ExecDataTable(string tableName) { SqlConnection conn = new SqlConnection(SqlConn.GetSqlConnStr()); m_cmd.Connection = conn; SqlDataAdapter dat = new SqlDataAdapter(m_cmd); DataTable dt = new DataTable(); try { dat.Fill(dt); } catch (InvalidOperationException ept) { throw new DBOpenException(ept.Message, ept); } catch (SqlException ept) { //WriteEventLog(ept.Message); throw new DBQueryException(ept.Message, ept); } catch (Exception ept) { ////Console.WriteLine(ept.Message); throw ept; } finally { // 确保数据库连接关闭 if (conn.State != ConnectionState.Closed) { conn.Close(); } } if (tableName != null) { dt.TableName = tableName; } return(dt); }
/// <summary> /// 执行Sql命令,返回影响结果数 /// </summary> /// <returns></returns> public int ExecNoQuery() { int pot = -1; SqlConnection conn = new SqlConnection(SqlConn.GetSqlConnStr()); try { conn.Open(); m_cmd.Connection = conn; pot = m_cmd.ExecuteNonQuery(); } catch (InvalidOperationException ept) { throw new DBOpenException(ept.Message, ept); } catch (SqlException ept) { //WriteEventLog(ept.Message); throw new DBQueryException(ept.Message, ept); } catch (Exception ept) { ////Console.WriteLine(ept.Message); throw ept; } finally { // 确保数据库连接关闭 if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(pot); }
/// <summary> /// 获取查询结果第一行第一列数据 /// </summary> /// <returns></returns> public object ExecScalar() { object result = null; SqlConnection conn = new SqlConnection(SqlConn.GetSqlConnStr()); try { conn.Open(); m_cmd.Connection = conn; result = m_cmd.ExecuteScalar(); } catch (InvalidOperationException ept) { throw new DBOpenException(ept.Message, ept); } catch (SqlException ept) { //WriteEventLog(ept.Message); throw new DBQueryException(ept.Message, ept); } catch (Exception ept) { ////Console.WriteLine(ept.Message); throw ept; } finally { // 确保数据库连接关闭 if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(result); }