//执行数据库语句 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;
}
