//执行数据库语句
public void ExecuteSql(string strSql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
//执行查询
public DataTable ExcuteSelect(string strSql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "table");
return ds.Tables["table"];
conn.Close();
}
//是否有数据
public bool HasData(string tablel)
{
SqlConnection conn = new SqlConnection(ConnectionString);
string strSql="select * from ["+tablel+"]";
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr.Read();
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="db">数据库名</param>
public void CreateDatabase(string db)
{
if (HasDB(db))
{
MessageBox.Show("数据库"+db+"已经存在");
}
else
{
try
{
SqlConnection conn = new SqlConnection(ConnectionString);
string strSql = "create database ["+db+ "]";
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("创建数据库"+db+ "成功");
conn.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message, "创建数据库"+db+"失败");
}
}
}
/// <summary>
/// 是否存在数据库
/// </summary>
/// <param name="db">数据库名</param>
/// <returns></returns>
public bool HasDB(string db)
{
SqlConnection conn = new SqlConnection(ConnectionString);
string strSql = "SELECT name FROM sys.databases WHERE name = N'"+db+"';";
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr.Read();
}
/// <summary>
/// 从指定数据库和表中读取某一列的数据存成一个字符串数据,并以<tab>分隔
/// </summary>
/// <param name="db">数据库名</param>
/// <param name="table_name">表名</param>
/// <param name="column_name">字段名</param>
/// <returns>返回一个字符串数据,并以<tab>分隔</returns>
public string GetColumnData( string db,string table_name, string column_name)
{
//返回值,错误时将返回空值
string colunm_data = "";
//连接字段值
StringBuilder col_data = new StringBuilder();
SqlConnection conn = new SqlConnection(ConnectionString);
//查询语句
string strSql = "use [" + db + "] select ["+column_name+"] from [" + table_name + "]";
//创建sqldataadapter对象
SqlDataAdapter adp = new SqlDataAdapter(strSql ,conn);
//创建一个数据集
DataSet ds = new DataSet();
try
{
adp.Fill(ds, table_name);
}
catch
{
MessageBox.Show ( "数据库查询错误,请重新设置查询条件");
adp.Dispose();
ds.Dispose();
return colunm_data;
}
if (ds.Tables[table_name].Rows.Count == 0)
{
MessageBox.Show ( "在数据库中没有相应的记录");
return colunm_data;
}
for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
{
//取出数据字段的值
col_data.Append(ds.Tables[table_name].Rows[i][column_name].ToString() + "/t");
colunm_data = col_data.ToString();
}
if (colunm_data == "")
{
MessageBox.Show ("在数据库中没有相应的记录");
}
else
{
MessageBox.Show ("查询成功");
}
return colunm_data;
}
/// <summary>
/// 插入数据到指定索引列
/// <param name="name">表名</param>
/// <param name="i">列的索引值</param>
/// <param name="data">数据</param>
/// <returns></returns>
public void InsertColumnData(string table, int i,string data)
{
try
{
SqlConnection conn = new SqlConnection(ConnectionString);
string col="";
for (int j = 0; j < i; j++)
{
col +="' '"+",";
}
col += "'" + data + "'";
string col1 = col;
string strSql = "insert into ["+table+"] values("+col1+"); ";
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("插入数据成功");
conn.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message, "插入数据失败");
}
}
/// <summary>
/// 根据某一列满足的指定条件去读取另一列需要的数据,并存成一个字符串数据
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="condition">条件</param>
/// <param name="conditionColumn">满足条件的列字段名</param>
/// <param name="column_name">需要的列字段名</param>
/// <returns>返回一个字符串数据,并以<tab>分隔</returns>
public string GetConditionColumnData(string table_name,string condition, string conditionColumn, string column_name)
{
//返回值,错误时将返回空值
string column_data = "";
//连接字段值
StringBuilder col_data = new StringBuilder();
SqlConnection conn = new SqlConnection(ConnectionString);
//查询语句
string strSql = " select [" + column_name + "] from [" + table_name + "] where "+conditionColumn + "='"+condition+"'";
//创建sqldataadapter对象
SqlDataAdapter adp = new SqlDataAdapter(strSql, conn);
//创建一个数据集
DataSet ds = new DataSet();
try
{
adp.Fill(ds, table_name);
}
catch
{
MessageBox.Show("数据库查询错误,请重新设置查询条件");
adp.Dispose();
ds.Dispose();
return column_data;
}
if (ds.Tables[table_name].Rows.Count == 0)
{
MessageBox.Show("在数据库中没有相应的记录");
return column_data;
}
for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
{
//取出数据字段的值
col_data.Append(ds.Tables[table_name].Rows[i][column_name].ToString() + "/t");
column_data = col_data.ToString();
}
if (column_data == "")
{
MessageBox.Show("在数据库中没有相应的记录");
}
else
{
MessageBox.Show("查询成功");
}
return column_data;
}
/// <summary>
///取得一个表中某一列中所有不同的值,返回一个数组~
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="column_name">列字段名</param>
/// <returns>返回一个数组</returns>
public ArrayList GetDistinctColumnData(string table_name, string column_name)
{
//返回的数组
ArrayList column_data = new ArrayList();
SqlConnection conn = new SqlConnection(ConnectionString);
//查询语句
string strSql = " select distinct [" + column_name + "] from [" + table_name + "] ";
//创建sqldataadapter对象
SqlDataAdapter adp = new SqlDataAdapter(strSql, conn);
//创建一个数据集
DataSet ds = new DataSet();
try
{
adp.Fill(ds, table_name);
}
catch
{
MessageBox.Show("数据库查询错误,请重新设置查询条件");
adp.Dispose();
ds.Dispose();
return null ;
}
if (ds.Tables[table_name].Rows.Count == 0)
{
MessageBox.Show("在数据库中没有相应的记录");
return null ;
}
for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
{
//取出数据字段的值
column_data.Add((ds.Tables[table_name].Rows[i][column_name].ToString()));
}
if (column_data.Count ==0 )
{
MessageBox.Show("在数据库中没有相应的记录");
}
else
{
MessageBox.Show("查询成功");
}
return column_data;
}