1. SQL Server数据库通过代码操作: 数据库创建,表创建,增删改查操作,可参考下面代码链接:
https://www.c-sharpcorner.com/article/create-a-sql-server-database-dynamically-in-C-Sharp/
其中需要注意的事项为:
private string ConnectionString ="Integrated Security=SSPI;" + "Initial Catalog=;" + Data Source=localhost;";
Integrated Security=SSPI 为用当前Windows账号密码进行登陆,Initial Catalog=指定数据库名字,等同于DataBase
2. 上面只能当前电脑访问数据库,如果想局域网访问数据库,或者外网访问数据库,可以参考下面步骤来:
首先要单独创建个数据的登陆账号,密码。(我之前用Windows账号,密码登陆一直登陆不上,才采用这种方式)
SQL Server 本地新建登陆名,密码方法如下链接:https://blog.csdn.net/SHK242673/article/details/106408489
3. 下面为SQL Server本地局域网连接代码:
private string ConnectionString = "SERVER=192.168.10.29;" + "UID=test;" + "PWD=test;" + "DATABASE=;" + "Persist Security Info=True;";
private string DataBase_Name = "My_DB.mdf";
private string DataBase_TableName = "my_table";
private SqlConnection sql_conn = null;
private SqlCommand sql_cmd = null;
创建数据库
private void CreateDataBase(string DataBase_Name)
{
// Create a connection
sql_conn = new SqlConnection(ConnectionString);
// Open the connection
if (sql_conn.State != ConnectionState.Open)
sql_conn.Open();
string sql_createDataBase = "CREATE DATABASE my_db ON PRIMARY"
+ "(Name=my_data, filename='" + DataBase_Name + "', size=10Mb,maxsize=100Mb, filegrowth=1Kb)";
ExecuteSQLStmt(sql_createDataBase);
MessageBox.Show("创建数据库: " + DataBase_Name + " 成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
private void ExecuteSQLStmt(string sql)
{
if (sql_conn.State == ConnectionState.Open)
sql_conn.Close();
if (System.IO.File.Exists(DataBase_Name ))
{
ConnectionString = "SERVER=192.168.10.29;" + "UID=test;" + "PWD=test;" + "DATABASE=my_db ;" + "Persist Security Info=True;";
}
MessageBox.Show("数据库 connectString: " + ConnectionString + " 成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
sql_conn.ConnectionString = ConnectionString;
sql_conn.Open();
sql_cmd = new SqlCommand(sql, sql_conn);
try
{
sql_cmd.ExecuteNonQuery();
}
catch (SqlException ae)
{
//MessageBox.Show(ae.Message.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
创建表:
private void CreateTable(string Table_Name)
{
// Open the connection
if (sql_conn.State == ConnectionState.Open)
sql_conn.Close();
ConnectionString = "SERVER=192.168.10.29;" + "UID=test;" + "PWD=test;" + "DATABASE=my_db ;" + "Persist Security Info=True;";
MessageBox.Show("创建数据表:ConnectionString: " + ConnectionString, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
sql_conn.ConnectionString = ConnectionString;
sql_conn.Open();
string sql_createTable = "CREATE TABLE " + Table_Name +
"(taskId BIGINT CONSTRAINT PKeyMyId PRIMARY KEY," +
"taskName CHAR(300), projectName CHAR(100), brandName CHAR(100))";
MessageBox.Show("创建数据表:sql_createTable: " + sql_createTable, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
sql_cmd = new SqlCommand(sql_createTable, sql_conn);
try
{
sql_cmd.ExecuteNonQuery();
}
catch(SqlException ae)
{
}
MessageBox.Show("创建数据表:" + Table_Name + "成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
查询数据库
private void querry_database(string select_name)
{
string sql_select = "select * from " + DataBase_TableName + " where taskName='" + select_name+ "'";
sql_cmd = new SqlCommand(sql_select, sql_conn);
using (SqlDataReader reader = sql_cmd.ExecuteReader())
{
while (reader.Read())
{
//获取表里面的字段信息
MessageBox.Show("taskId: " + reader.GetInt64(0) + " taskName: " + reader.GetString(1) + " projectName" + reader.GetString(2) + " brandName: " + reader.GetString(3), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
上面代码为运行成功的部分核心代码,参考这个来,稍微修改就可以用。
突然搞C# SQL Server 有点蒙,先记录下,给初学者分享下。