数据库操作整理

1、连接字符串

       1）直接配置数据源的时候，保存链接字符串

       2）在项目设计器中“设置”中添加链接字符串

在app中将会有

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="WindowsApplication3.Properties.Settings.MYSALEDBConnectionString"
            connectionString="Data Source=computer;Initial Catalog=MYSALEDB;User ID=sa;Password=123456"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

 

在读取的链接字符串的时候

添加using System.Configuration;及引用

System.Configuration.ConfigurationManager.ConnectionStrings["WindowsApplication3.Properties.Settings.MYSALEDBConnectionString"].ToString()

即可得到连接字符串

2、命令处理数据（sqltext）

      1）只返回数据的情况,用dataread,将dataread绑定到datagridview有两种方法

           private void GetData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(sqlStr))
                {
                    string strSql = "select * from 采购客户 where 1=1 ";
                    if (this.txtCustomerName.Text != string.Empty)
                    {
                        strSql += " and 客户名称 like '%" + this.txtCustomerName.Text.Replace("'", "").Replace("/"", "") + "%'";
                    }
                    if (connection.State != ConnectionState.Connecting) connection.Open();
                    SqlCommand command = new SqlCommand(strSql, connection);
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    dgvData.DataSource = dt;

                    //this.bindingSource1.DataSource = reader;
                    //dgvData.DataSource = bindingSource1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

       关于创建command,有很多方法：

       1、SqlCommand command=new SqlCommand(sqltext,connection);

       2、SqlCommand command = connection.CreateCommand();
             command.CommandText = sqltext;

     

      存储过程：带参数

Create PROCEDURE GetCutomer
 @customerName nvarchar(20)
 /*
 (
 @parameter1 int = 5,
 @parameter2 datatype OUTPUT
 )
 */
AS
 if(@customerName<>'')
  select * from [采购客户] where 客户名称 like '%'+@customerName+'%'
 else
  select * from [采购客户]
 RETURN

 

private void GetDataProcedure()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(sqlStr))
                {
                    if (connection.State != ConnectionState.Connecting) connection.Open();
                    SqlCommand command = new SqlCommand("GetCutomer", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    string m = "";
                    if (this.txtCustomerName.Text != string.Empty) m = this.txtCustomerName.Text;
                    command.Parameters.AddWithValue("@customerName", this.txtCustomerName.Text);
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    dgvData.DataSource = dt;

                    //this.bindingSource1.DataSource = reader;
                    //dgvData.DataSource = bindingSource1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }