dataGridView 粘贴数据并更新到数据库及发送邮件(二)

C#WindowsForm 不会自动建立app.config配置文件，需要手动添加。
因为部分内容要做成可配置，所以这里需要用到app.config
效果如下：

主要代码如下：
form1

using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Linq;
using System.IO;
using System.Net.Mail;
namespace Test07
{
    public partial class Form1 : Form
    {
        public static int effectedRows = 0;

        public static int effectedRowsFirstAndTwo = 0;
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_loading(object sender, EventArgs e)
        {
            log.Items.Add("=====loading data=====");
            dataGridView1.DataSource = null;
        }

        private void button1_Click(object sender, EventArgs e)
        {

            //创建命令重建对象
            // SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
            //更新数据
            try
            {
                //update
                log.Items.Add("=====update 3,4====");
                string str = ConfigurationManager.ConnectionStrings["db_conn"].ConnectionString;
                SqlConnection conn = new SqlConnection(str);
                string update34 = File.ReadAllText(GetPath("update34.sql"));
                log.Items.Add(update34);
                string code = File.ReadAllText(GetPath("code.txt"));
                log.Items.Add(code);
                update34 = update34.Replace("{code}", code);
                log.Items.Add(update34);
                conn.Open();
                SqlCommand cmd = new SqlCommand(update34, conn);
                cmd.CommandTimeout = 10000;
                effectedRows = cmd.ExecuteNonQuery();
                log.Items.Add("effect row : " + effectedRows + "row");
                log.Items.Add("=====update 1,2====");
                string update12 = File.ReadAllText(GetPath("update12.sql"));
                update12 = update12.Replace("{code}", code);
                log.Items.Add(update12);
                SqlCommand cmdFirstAndTwo = new SqlCommand(update12, conn);
                cmdFirstAndTwo.CommandTimeout = 10000;
                effectedRowsFirstAndTwo = cmdFirstAndTwo.ExecuteNonQuery();
                log.Items.Add("effect row : " + effectedRowsFirstAndTwo + "row");
                //query
                log.Items.Add("=====query result====");
                string query = File.ReadAllText(GetPath("query.sql"));
                query = query.Replace("{code}", code);
                log.Items.Add(query);
                SqlCommand sqlcmd = new SqlCommand(query, conn);
                sqlcmd.CommandTimeout = 1000000;
                SqlDataAdapter readData = new SqlDataAdapter(sqlcmd);
                DataSet dtSetEnd = new DataSet();
                readData.Fill(dtSetEnd);
                System.Data.DataTable Table = dtSetEnd.Tables[0];
                log.Items.Add("=====query end====");
                log.Items.Add("export excel");
                string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "update" + DateTime.Now.ToString("yyyy-MM-dd") + ".csv");
                File.AppendAllText(fileName, "");
                dataGridView1.DataSource = Table;
                SaveCSV(Table, fileName);
                sendMail(fileName);
                log.Items.Add("=====End All=====");


            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        //send Mail
        public static void sendMail(string fileName)
        {
            System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
            string[] ToUser = ConfigurationManager.AppSettings["mail_to"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string[] CCUser = ConfigurationManager.AppSettings["mail_cc"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string Form = ConfigurationManager.AppSettings["mail_from"];
            string ToUserSum = null;
            string CCUserSum = null;
            //string ToUserSum = String.Join(",", ToUser);
            for (int i = 0; i < ToUser.Length; i++)
            {
                msg.To.Add(ToUser[i]);
                ToUserSum += ToUser[i] + ',';
            }

            for (int i = 0; i < CCUser.Length; i++)
            {
                msg.CC.Add(CCUser[i]);
                CCUserSum = CCUser[i] + ',';
            }

            msg.From = new MailAddress(Form, ConfigurationManager.AppSettings["mail_from_name"], System.Text.Encoding.UTF8);
            msg.Subject = "促销券" + DateTime.Now.ToString("yyyy-MM-dd");//title    
            msg.SubjectEncoding = System.Text.Encoding.UTF8;//title encode    
            msg.Body = "<div>Hi Alex</div><p>所有券已更新</p>已使用券共" + effectedRows + "用DominosMarkRepeat-" + DateTime.Now.ToString("yyyyMMdd") + "标记<br/>未使用券共" + effectedRowsFirstAndTwo + "用DominosMarkOLOUsed-" + DateTime.Now.ToString("yyyyMMdd") + "标记<br/>BR<p>---Denglin</p>";//body    
            msg.BodyEncoding = System.Text.Encoding.UTF8;//contentencode    
            msg.IsBodyHtml = true;// Is html?  
            msg.Priority = MailPriority.Normal;//priority 
            msg.Attachments.Add(new Attachment(fileName));
            SmtpClient client = new SmtpClient();
            client.Credentials = new System.Net.NetworkCredential("yourusername", "password");//regMail and password   
            client.Host = "smtp.exmail.qq.com";
            object userState = msg;
            try
            {
                //client.SendAsync(msg, userState);

                client.Send(msg);
                MessageBox.Show("mail send success");
            }
            catch (System.Net.Mail.SmtpException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private static void ExportLog(string msg)
        {

            //log.Items.Add("update end");


        }
        private static string GetPath(string fileName)
        {
            return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fileName);
        }
        public static void SaveCSV(System.Data.DataTable dt, string fileName)
        {
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);//读取文件，fileMode类型为Create即先删除后新建，fileAccess权限为写
            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);//转下编码
            string data = "";

            //write colData
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                data += dt.Columns[i].ColumnName.ToString();
                if (i < dt.Columns.Count - 1)
                {
                    data += ",";
                }
            }
            sw.WriteLine(data);

            //weite RowData
            if (dt.Rows.Count > 0)
            {
                Console.WriteLine("start write rows data");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    data = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        data += dt.Rows[i][j].ToString() == "" ? "null" : dt.Rows[i][j].ToString();
                        if (j < dt.Columns.Count - 1)
                        {
                            data += ",";
                        }
                    }
                    sw.WriteLine(data);
                }
            }
            else
            {
                Console.WriteLine("data empty");
            }
            sw.Close();
            fs.Close();

        }
        public void DataGridViewEnablePaste(DataGridView p_Data)
        {
            if (p_Data == null)
                return;
            p_Data.KeyDown += new KeyEventHandler(p_Data_KeyDown);
        }
        public static void DataGirdViewCellPaste(DataGridView DBGrid)
        {
            try
            {
                // 获取剪切板的内容，并按行分割  
                string pasteText = "";
                pasteText = Clipboard.GetText();
                MessageBox.Show(pasteText);
                if (string.IsNullOrEmpty(pasteText))
                    return;
                if (pasteText == "pasteText")
                {
                    return;
                }
                int tnum = 0;
                int nnum = 0;
                //获得当前剪贴板内容的行、列数搜索
                for (int i = 0; i < pasteText.Length; i++)
                {
                    if (pasteText.Substring(i, 1) == "\t")
                    {
                        tnum++;
                    }
                    if (pasteText.Substring(i, 1) == "\n")
                    {
                        nnum++;
                    }
                }
                Object[,] data;
                //粘贴板上的数据来自于EXCEL时，每行末都有\n，在DATAGRIDVIEW内复制时，最后一行末没有\n
                if (pasteText.Substring(pasteText.Length - 1, 1) == "\n")
                {
                    nnum = nnum - 1;
                }
                tnum = tnum / (nnum + 1);
                data = new object[nnum + 1, tnum + 1];//定义一个二维数组
                String rowstr;
                rowstr = "";
                //MessageBox.Show(pasteText.IndexOf("B").ToString());
                //对数组赋值
                for (int i = 0; i < (nnum + 1); i++)
                {
                    for (int colIndex = 0; colIndex < (tnum + 1); colIndex++)
                    {
                        //一行中的最后一列
                        if (colIndex == tnum && pasteText.IndexOf("\r") != -1)
                        {
                            rowstr = pasteText.Substring(0, pasteText.IndexOf("\r"));
                        }
                        //最后一行的最后一列
                        if (colIndex == tnum && pasteText.IndexOf("\r") == -1)
                        {
                            rowstr = pasteText.Substring(0);
                        }
                        //其他行列
                        if (colIndex != tnum)
                        {
                            rowstr = pasteText.Substring(0, pasteText.IndexOf("\t"));
                            pasteText = pasteText.Substring(pasteText.IndexOf("\t") + 1);
                        }
                        data[i, colIndex] = rowstr;
                    }
                    //截取下一行数据
                    pasteText = pasteText.Substring(pasteText.IndexOf("\n") + 1);
                }
                //获取当前选中单元格所在的列序号
                int curntindex = DBGrid.CurrentRow.Cells.IndexOf(DBGrid.CurrentCell);
                //获取获取当前选中单元格所在的行序号
                int rowindex = DBGrid.CurrentRow.Index;
                //MessageBox.Show(curntindex.ToString ());
                for (int j = 0; j < (nnum + 1); j++)
                {
                    for (int colIndex = 0; colIndex < (tnum + 1); colIndex++)
                    {
                        if (!DBGrid.Columns[colIndex + curntindex].Visible)
                        {
                            continue;
                        }
                        if (!DBGrid.Rows[j + rowindex].Cells[colIndex + curntindex].ReadOnly)
                        {
                            DBGrid.Rows[j + rowindex].Cells[colIndex + curntindex].Value = data[j, colIndex];
                        }
                    }
                }
                Clipboard.Clear();
            }
            catch
            {
                Clipboard.Clear();
                //MessageBox.Show("粘贴区域大小不一致");
                return;
            }
        }
        public void p_Data_KeyDown(object sender, KeyEventArgs e)
        {

            if (Control.ModifierKeys == Keys.Control && e.KeyCode == Keys.V)
            {

                if (sender != null && sender.GetType() == typeof(DataGridView))

                    // 调用上面的粘贴代码
                    DataGirdViewCellPaste((DataGridView)sender);
            }
            else
            {
                Clipboard.SetDataObject(dataGridView1.GetClipboardContent().GetData(DataFormats.Text).ToString());
            };
        }
        public void p_Data_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.C && e.Modifiers == Keys.Control)
            {
                Clipboard.SetText(this.dataGridView1.GetClipboardContent().GetData(DataFormats.Text).ToString());

            }
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="db_conn" connectionString="server=10.1.1.2344;database=DB_wdl;uid=wdl;pwd=111111;MultipleActiveResultSets=true"/>
    </connectionStrings>
    <appSettings>
        <add key="IfNewAdd" value="1"/>
        <add key="mail_from" value="denglin.wang@edaysoft.cn"/>
        <add key="mail_from_name" value="wangdenglin"/>
        <add key="mail_to" value=""/>
        <add key="mail_cc" value="denglin.wang@edaysoft.cn"/>
        <add key="mail_host" value="smtp.exmail.qq.com"/>
        <add key="mail_uid" value="denglin.wang@edaysoft.cn"/>
        <add key="mail_pwd" value="password"/>
    </appSettings>
</configuration>

以上就是主要代码