C#与SQL Server数据库

最近需要通过程序连接数据库使用，本篇作为数据库操作相关内容的第一篇，仅记录查资料以及自己尝试可行的笔记内容，具体关于在项目中的真实应用会在后续数据库内容中更新。

连接数据库

//SQL查询语句
  public DataTable query(string sql)
  {
   //server=127.0.0.1;
   DataTable dt=new DataTable();
   //DataRow dr;
   string connString = "server=127.0.0.1;uid=sa;pwd=;database=TJPTTC";
   SqlConnection conn = new SqlConnection(connString);
   SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
   adapter.Fill(dt);
   return dt;
  }
//SQL非查询语句
  public void noQuery(string sql)
  {
   //server=127.0.0.1;
   string connString = "server=GLASSESONION;uid=sa;pwd=;database=TJPTTC";
   SqlConnection conn = new SqlConnection(connString);
   SqlCommand cmd = new SqlCommand(sql,conn);
   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close(); 
  }

判断数据库是否存在，存在就删除：未细读

判断表是否存在

1	IF Object_id ('xxxx','xx') Is Null Select 0 Else Select 1

if object_id('对象名') is not null  执行语句
--尽量写上对象类型，避免因为同名现象，而查询出错
--或者
if object_id('对象名','对象类型') is not null  执行语句
--或者
if object_id(N'对象名',N'对象类型') is not null  执行语句
--等同于：select ID from sysobjects where name='对象名' and type='对象类型'

type如下列表：

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

大家都知道在数据库中有一个系统表sysobjects，里面存储了数据库各个对象的信息。可以查询下看看结果。可以看出每个对象都有一个ID，这个表存储了表，存储过程，触发器，视图等相关信息。注意：字段没有。

object_id 就是根据对象名称返回该对象的id.
object_name 是根据对象id返回对象名称.


select object_id(对象名)等同于:
select id from sysobjects where name=对象名

select object_name(id号)等同于:
select name from sysobjects where id=id号

SQL SERVER 2000以上版本都支持这个函数。

图形化数据库创建和文件存储路径

public partial class Form1 : Form
   {
       public string strDatabaseName = string.Empty;//创建数据库时数据库名称
       public string strDataFilePath = string.Empty;//数据库文件、日志存储位置
       SqlConnection conn = new SqlConnection("Server=localhost;User ID=sa;pwd=0123456789");
       public Form1()
       {
           InitializeComponent();

       }

       private void btnCreateDatabase_Click(object sender, EventArgs e)
       {
           strDatabaseName = this.TextDatabaseName.Text;
           strDataFilePath = this.TextFileStoragePath.Text;
           DataBaseOperate.CreateDatabase(strDatabaseName, strDataFilePath);//创建数据库
       }


       /// <summary>
       /// 图形化文件存储路径
       /// </summary>
       private void BtnFileStroagePath_Click(object sender, EventArgs e)
       {
           FolderBrowserDialog dialog = new FolderBrowserDialog();
           dialog.Description = "请选择文件路径";
           string foldPath = "";
           if (dialog.ShowDialog() == DialogResult.OK)
           {
               foldPath = dialog.SelectedPath + @"\";
           }
           if (foldPath != string.Empty)
           {
               TextFileStoragePath.Text = foldPath;
           }
       }

       private void TextDatabaseName_TextChanged(object sender, EventArgs e)
       {
           if (TextDatabaseName.Text != string.Empty)
           {
               //默认数据库文件存储位置
               TextFileStoragePath.Text = "C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\";
           }
       }

/// <summary>
     /// 动态生成数据库
     /// </summary>
     /// <param name="strDatabaseName">创建的数据库名称|逻辑名称</param>
     /// <param name="strDataFilePath">文件存储路径</param>
     public static void CreateDatabase(string strDatabaseName, string strDataFilePath)
     {
         SqlConnection conn= new SqlConnection("Server=localhost;User ID=sa;pwd=0123456789");//连接本地服务器
         conn.Open();
         StringBuilder Sql_Command = new StringBuilder();
         Sql_Command.Append("If Not Exists ");
         Sql_Command.Append("(");
         Sql_Command.Append("Select * ");
         Sql_Command.Append("From ");
         Sql_Command.Append("master.dbo.sysdatabases ");
         Sql_Command.Append("where ");
         Sql_Command.Append("name = '" + strDatabaseName + "'");
         Sql_Command.Append(") ");

         string strSQLCmd = Sql_Command.ToString() + "CREATE DATABASE "
                  + strDatabaseName + " ON PRIMARY " +//数据库名称
                  "(NAME = " + strDatabaseName + "_Data, " +//逻辑名称
                  "FILENAME = '" + strDataFilePath + strDatabaseName + "Data.mdf')" +//文件存储路径
                 "LOG ON (NAME = " + strDatabaseName + "_Log, " +//数据库日志文件
                 "FILENAME = '" + strDataFilePath + strDatabaseName + "Log.ldf')";//日志文件路径            
         
         SqlCommand myCommand = new SqlCommand(strSQLCmd, conn);
         try
         {
             Conn.Open();
             myCommand.ExecuteNonQuery();
             MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
         }
         catch (SqlException ex)
         {
             string strDataInfo = "数据库创建失败。" + DateTime.Now + "\r\n";
             string strException = string.Format(strDataInfo + "异常类型：{0}\r\n异常消息：{1}\r\n异常信息：{2}\r\n", ex.GetType().Name, ex.Message, ex.StackTrace);
             MessageBox.Show("数据库创建失败。" + ex.Message.ToString());
             FileOperate.WriteLog(strException);
         }
         finally
         {
             if (Conn.State == ConnectionState.Open)
             {
                 Conn.Close();
             }
         }
     }

向表中添加数据

// 向表中添加记录  
// string sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
// "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) ";
// cmd = new SqlCommand(sql, Conn);
// cmd.ExecuteNonQuery();