参数化导入、导出数据

1、导入数据

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace 参数导入
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {StreamReader reader = new StreamReader("name1.txt");//要导入的文件
            using (reader)
            {
                string sqlconn = @"server=.\sqlexpress;database=MyData;uid=sa;pwd=123";
                string sql = @"insert into person(fname,fgender,fage) values (@name,@gender,@age)";//参数化数据要导入的数据
                using (SqlConnection conn = new SqlConnection(sqlconn))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                        int count = 0;
                        string res = string.Empty;
                        while ((res = reader.ReadLine()) != null)
                        {
                            string[] temp = res.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);//循环读取，并将文本文件每行分段
                            if (temp.Length != 3)
                            {
                                continue;
                            }
                           
                                string name = temp[0];
                                string gender = temp[1];
                                int age = Convert.ToInt32(temp[2]);
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@name", name);
                                cmd.Parameters.Add("@gender", gender);
                                cmd.Parameters.Add("@age", age);
                                count += cmd.ExecuteNonQuery();
                        }
                     MessageBox.Show(string.Format("有{0}行受影响", count));
                    }
                }
            }
        }
    }
}

2、导出数据

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _06参数化查询
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            string strConn = @"server=.\sqlexpress;database=MyDataBase;uid=sa;pwd=123";
            
            // 写SQL语句时得使用变量
            string sql = @"select count(*) from tblLogin where fname=@name and fpwd =@pwd";

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    // 使用查询的时候需要为变量赋值
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new SqlParameter("@name", txtUid.Text.Trim()));
                    cmd.Parameters.AddWithValue("@pwd", txtPwd.Text.Trim());


                    conn.Open();

                    int res = Convert.ToInt32(cmd.ExecuteScalar());
                    if (res > 0)
                    {
                        MessageBox.Show("登录成功");
                    }
                    else
                    {
                        MessageBox.Show("Error");
                    }
                }
            }
        }
    }
}