//method
#region 获取数据表
internal DataTable GetData(string _sql)
{
DataSet dsRst = new DataSet();
DataTable dtRst = new DataTable();
Conn cn = new Conn();
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
};
SqlDataAdapter myDataAdapter
= new SqlDataAdapter(_sql, cn.Connection);
myDataAdapter.Fill(dsRst);
if (cn.isOpen)
{
cn.Connection.Close();
};
dtRst = dsRst.Tables[0];
cn = null;
return dtRst;
}
internal bool Insert
(string _tableName, List<string> _field, List<string> _value)
{
bool bRst = false;
//拼装语句
string sql = "INSERT INTO " + _tableName + " (";
if (_field.Count > 0)
{
for (int i = 0; i < _field.Count; i++)
{
if (i == _field.Count - 1)
{
sql += _field[i] + ")";
}
else
{
sql += _field[i] + ",";
};
};
};
sql += " VALUES (";
if (_value.Count > 0)
{
for (int i = 0; i < _value.Count; i++)
{
if (i == _value.Count - 1)
{
sql += "'" + _value[i] + "')";
}
else
{
sql += "'" + _value[i] + "',";
}
};
};
Conn cn = new Conn();
try
{
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
};
SqlCommand MyCommand = new SqlCommand(); //定义一个数据库操作指令
MyCommand.Connection = cn.Connection;
MyCommand.CommandText = sql;
int iRst = MyCommand.ExecuteNonQuery();
if (iRst > 0)
{
bRst = true;
}
else
{
bRst = false;
}
}
catch (Exception)
{
bRst = false;
throw;
}
finally
{
if (cn.isOpen)
{
cn.Connection.Close();
};
}
return bRst;
}
internal int ExcuteQuerryByTran(string sql, object[] objParam)
{
try
{
Conn cn = new Conn();
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
};
SqlCommand cmd = new SqlCommand(sql, cn.Connection);
for (int j = 0; j < objParam.Length; j++)
{
if (objParam[j] == null)
{
objParam[j] = "";
}
cmd.Parameters.Add(new SqlParameter("Param" + j.ToString(), objParam[j]));
};
int iRst = cmd.ExecuteNonQuery();
if (cn.isOpen)
{
cn.Connection.Close();
cn.isOpen = false;
};
return iRst;
}
catch (Exception ex)
{
if (!ex.Data.Contains("sql"))
{
ex.Data.Add("sql", sql);
}
throw ex;
};
}
internal bool Delete(string _tabName, string _where)
{
bool bRst = false;
string sql = @" DELETE FROM " + _tabName + " WHERE " + _where;
Conn cn = new Conn();
try
{
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
};
SqlCommand MyCommand
= new SqlCommand(); //定义一个数据库操作指令
MyCommand.Connection = cn.Connection;
MyCommand.CommandText = sql;
int iRst = MyCommand.ExecuteNonQuery();
if (cn.isOpen)
{
cn.Connection.Close();
cn.isOpen = false;
};
if (iRst > 0)
{
bRst = true;
}
else
{
bRst = false;
};
}
catch (Exception)
{
throw;
}
finally
{
if (cn.isOpen)
{
cn.Connection.Close();
cn.isOpen = false;
};
}
return bRst;
}
internal bool UpdateMany
(string _tabName, List<string> _field, List<string> _value,
string _where)
{
bool bRst = false;
string strSQL = @"UPDATE " + _tabName + " SET ";
for (int i = 0; i < _field.Count; i++)
{
if (i < _field.Count - 1)
{
strSQL += _field[i] + " = '" + _value[i] + "', ";
}
else if (i == _field.Count - 1)
{
strSQL += _field[i] + " = '" + _value[i] + "' ";
break;
};
};
strSQL += " WHERE " + _where;
Conn cn = new Conn();
try
{
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
};
SqlCommand MyCommand
= new SqlCommand(); //定义一个数据库操作指令
MyCommand.Connection
= cn.Connection;
MyCommand.CommandText = strSQL;
int iRst = MyCommand.ExecuteNonQuery();
if (cn.isOpen)
{
cn.Connection.Close();
};
if (iRst > 0)
{
bRst = true;
}
else
{
bRst = false;
};
}
catch (Exception)
{
throw;
}
finally
{
if (cn.isOpen)
{
cn.Connection.Close();
};
};
return bRst;
}
internal int GetCount(string _table, string _where)
{
int iRst = 0;
string strSql = "SELECT COUNT(1) FROM "
+ _table + " WITH(NOLOCK) WHERE " + _where;
Conn cn = new Conn();
try
{
SqlCommand MyCommand = new SqlCommand(); //定义一个数据库操作指令
MyCommand.Connection = cn.Connection;
MyCommand.CommandText = strSql;
if (!cn.isOpen)
{
cn.Connection.Open();
cn.isOpen = true;
}
var rst = MyCommand.ExecuteScalar();
iRst = Int32.Parse(rst.ToString());
}
catch (Exception e)
{
iRst = 0;
throw;
}
finally
{
if (cn.isOpen)
{
cn.Connection.Close();
};
}
return iRst;
}
