一、其中包括创建数据库,数据表,插入字段,更新字段,已Windows身份连接数据库等操作
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;
二、完整代码如下(这是我代码中定义的一个类)
namespace FORM{
class SQL_Database
{
///-----------------------------------------------------
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="str_FilePath">数据库保存路径</param>
/// -----------------------------------------------------
public void SQL_Database_Create(string str_FilePath)
{
string ConnectionString = "Integrated Security=SSPI;Initial Catalog =; Data Source = localhost;";
//此为连接字符串//其中SSPI为Windows身份登录
//str_FilePath:为操作数据文件路径和文件名 //.mdf文件: 表示主要数据文件(存储数据的文件)
//Test_Name: 数据库名:如果不设置日志文件名则与数据库同名.
//大致理解为数据表来代表物理文件,逻辑文件就是视图
string sql = "CREATE DATABASE Test ON PRIMARY(name= Database_logic, filename ='" + str_FilePath +
"',size = 3MB, maxsize=5MB, filegrowth=10%)";
SqlConnection conn = new SqlConnection(ConnectionString); //连接数据库
conn.Open();
SqlCommand judeg_Sql_exist = new SqlCommand("select count(*) from sys.databases where name='Test'", conn);//判断是否存在数据库
int temp = int.Parse(judeg_Sql_exist.ExecuteScalar().ToString());
if (temp <= 0)//数据库不存在,则进行建库
{
SqlCommand cmd = new SqlCommand(sql, conn);
//此为建立数据库命令返回,如果提示目录查找失败,出现操作系统错误 5(拒绝访问。)。//需给当前执行程序文件夹authenticated users 完全控制权限
cmd.ExecuteNonQuery();
}
conn.Close();
}
///-----------------------------------------------------
/// <summary>
/// 创建SQL数据表方法
/// </summary>
/// <param name="str_Dbname">数据库表名</param>
/// -----------------------------------------------------
public void SQL_DataTable_Create(string str_Dbname)
{
//以Windows身份连接SQL数据库;
string ConnectionString = "Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;";
string sql_table = "CREATE TABLE [" + str_Dbname + "](Datagroup CHAR(8))";//创建表名并增加第一个字段
SqlConnection conn = new SqlConnection(ConnectionString); //连接数据库
conn.Open();//打开连接
SqlCommand judge_sql_exist = new SqlCommand("select * from sys.tables where name = 'DateTable'", conn);//判断表是否存在
SqlDataReader reader = judge_sql_exist.ExecuteReader();//将执行结果反馈
if (reader.HasRows == false) //数据表不存在,则进行建表
{
conn.Close();
conn.Open();//打开连接
SqlCommand cmd_Table = new SqlCommand(sql_table, conn);//发送连接命令
cmd_Table.ExecuteNonQuery();
}
//回应执行结果
conn.Close();
}
///-----------------------------------------------------
/// <summary>
/// 增加数据组数
/// </summary>
/// <param name="TableName">数据表名</param>
/// <param name="number">组数</param>
/// -----------------------------------------------------
public void SQL_Alter_Group(string TableName, int number)
{
string SQL_Arr = "insert into [" + TableName + "](Datagroup) values( 'Group" + number + "')"; //增加数据组数
SqlConnection Sql_conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;");
Sql_conn.Open();
SqlCommand cmd_Table = new SqlCommand(SQL_Arr, Sql_conn);
cmd_Table.ExecuteNonQuery();
Sql_conn.Close();
}
///-----------------------------------------------------
/// <summary>
/// 增加数据库字段
/// </summary>
/// <param name="Tablrname">表名</param>
/// <param name="Total_length">总共要增长的数</param>
/// <param name="maxnumber">元素最大个数</param>
/// -----------------------------------------------------
public void SQL_Alter_colum(string Tablrname, int Total_length, int maxnumber)
{
SqlConnection Sql_conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;");
Sql_conn.Open();
for (int i = 0; i < Total_length - maxnumber; i++)
{
int j = maxnumber + i + 1;
string Sql_columns = "alter table [" + Tablrname + "] add [element" + j + "] float";//增加数据库字段
SqlCommand SqlCmd_colums = new SqlCommand(Sql_columns, Sql_conn);
try
{
SqlCmd_colums.ExecuteNonQuery();
}
catch
{; }
}
Sql_conn.Close();
maxnumber = Total_length;
}
///-------------------------------------------------------------------
/// <summary>
/// 更新数据
/// </summary>
/// <param name="TableName">数据表名称</param>
/// <param name="Total_length">该数据组长度</param>
/// <param name="Access_arr_after">数据组的第几个元素</param>
/// <param name="Cal_m">第几组数据元素</param>
/// <param name="sql_rownum">数据库中该组元素显示名称</param>
/// <param name="DA">数据存储数组</param>
/// ------------------------------------------------------------------
public void SQL_Updata(string TableName, int Total_length, int Cal_m, int sql_rownum, List<List<float>> DA)
{
int Access_arr_after = 0;//定义每组元素的个数
SqlConnection Sql_conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;");
Sql_conn.Open();
for (int arr_after = 0; arr_after < Total_length; arr_after++)// DA.GetLength(1)计算总共有多少列,即每一组有多少个数据
{
//将数据组数写入数据库中
Access_arr_after = arr_after + 1;
string Sql_colum_Arr = "update [" + TableName + "] set element" + Access_arr_after + "='" + DA[Cal_m][arr_after] +
"' where [Datagroup] = 'Group" + sql_rownum + "'";//需要注意数据格式;切记切记
SqlCommand Cmd_colums_arr = new SqlCommand(Sql_colum_Arr, Sql_conn);
Cmd_colums_arr.ExecuteNonQuery();
}
Sql_conn.Close();
}
///----------------------------------------------------------------
/// <summary>
/// 将平均值保存至数据库
/// </summary>
/// <param name="TableName">数据表</param>
/// <param name="rownumber">当前总的的组数</param>
/// <param name="Aer_Array">平均是数组</param>
/// <returns>返回保存数据结果1:表示保存成功</returns>
/// -------------------------------------------------------------
public int SQL_Average(string TableName, int rownumber, List<float> Aer_Array)
{
int Data_save_flag = 0;
try
{
string Sql_column = "alter table [" + TableName + "] add Average float";//增加数据库字段
SqlConnection Sql_conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;");
Sql_conn.Open();
SqlCommand SqlCmd_colums = new SqlCommand(Sql_column, Sql_conn);
SqlCmd_colums.ExecuteNonQuery();
Sql_conn.Close();
}
catch (Exception ex)
{
// MessageBox.Show("提示: "+ex);
if (ex.Message.ToString() == "各表中的列名必须唯一。在表 'DateTable' 中多次指定了列名 'Average'。")
{
;//MessageBox.Show("列名已存在");
}
else
{
MessageBox.Show("未知错误,请联系管理员!!!");
}
}
SqlConnection Sql_conn1 = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test;Data Source=localhost;");
Sql_conn1.Open();
for (int i = 1; i <= rownumber; i++)
{
string Sql_colum_Arr_aver = "update [" + TableName + "] set [Average]='" + Aer_Array[i - 1] +
"' where [Datagroup] = 'Group" + i + "'";
SqlCommand Cmd_colums_arr = new SqlCommand(Sql_colum_Arr_aver, Sql_conn1);
Data_save_flag = Cmd_colums_arr.ExecuteNonQuery();//执行命令并返回结果,如果结果为1,则数据保存成功
}
Sql_conn1.Close();
return Data_save_flag;
}
}
}