如何在C＃中将数据插入MS Sql数据库 [英] How to Insert Data into MS Sql database in C#

问题描述

我有c＃字符串看起来像这样：

字符串名称是finaloutput

i have c# String look like this:
String name is finaloutput

"IP,C1,PktUp,PktDown,Upstream,Downstream,C2,C3,C4,C5\r\n192.168.1.56,0,77776,98364,9203233,96637437,0,0,0,0\r\n192.168.1.12,0,80718,105922,11845169,94299988,0,0,0,0"

i希望将字符串数据插入sql数据库表

这是我的列标题

i want to insert String data to sql database table
this is my column header

IP,C1,PktUp,PktDown,Upstream,Downstream,C2,C3,C4,C5

这是我的数据行

this is my data row

192.168.1.56,0,77776,98364,9203233,96637437,0,0,0,0
192.168.1.12,0,80718,105922,11845169,94299988,0,0,0,0

我的表名是用法

我的表列名称是

my table name is usage
my table column name is

IP C1 PktUp PktDown Upstream Downstream C2 C3 C4 C5

如何插入数据到我的桌子。

i试试这段代码，但插入第一行o nly。

how to insert data to my table.
i try this code but insert first row only.

private void button1_Click(object sender, EventArgs e)
{
    string finaloutput = "IP,C1,PktUp,PktDown,Upstream,Downstream,C2,C3,C4,C5\r\n192.168.1.56,0,77776,98364,9203233,96637437,0,0,0,0\r\n192.168.1.12,0,80718,105922,11845169,94299988,0,0,0,0";

    string[] fields = finaloutput.Split(',');
    using (SqlConnection con = new SqlConnection(@"Data Source='localhost';Initial Catalog='NetUsage';User ID='sa';Password='tstc123'"))
    {

        con.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO usage(IP, Upstream, Downstream) VALUES (@IP, @Upstream, @Downstream)", con);
        cmd.Parameters.AddWithValue("@IP", fields[0].ToString());
        cmd.Parameters.AddWithValue("@Upstream", fields[4].ToString());
        cmd.Parameters.AddWithValue("@Downstream", fields[5].ToString());
        cmd.ExecuteNonQuery();
    }
}

看起来像这样

look like this

IP  Upstream  Downstream                                         
--- --------- -----------
IP  Upstream   Downstream 

i想要这个

i want to this

IP            Upstream   Downstream                                         
---           ---------  -----------
192.168.1.56  9203233    96637437 
192.168.1.12  11845169   94299988 

Plz帮助我，我不熟悉c＃

推荐答案

你必须在拆分之前拆分线每列值。

这样：

You have to split the lines before splitting each column value.
This way:

string finaloutput = "IP,C1,PktUp,PktDown,Upstream,Downstream,C2,C3,C4,C5\r\n192.168.1.56,0,77776,98364,9203233,96637437,0,0,0,0\r\n192.168.1.12,0,80718,105922,11845169,94299988,0,0,0,0";

string[] lines = finaloutput.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);

string line;
string[] fields;

using (SqlConnection con = new SqlConnection(@"Data Source='localhost';Initial Catalog='NetUsage';User ID='sa';Password='tstc123'")) {

   con.Open();

   using (SqlCommand cmd = new SqlCommand("INSERT INTO usage(IP, Upstream, Downstream) VALUES (@IP, @Upstream, @Downstream)", con)) {

      cmd.Parameters.Add("@IP");
      cmd.Parameters.Add("@Upstream");
      cmd.Parameters.Add("@Downstream");

      // We start at index 1 as the first row (index 0) seems to hold column headers
      for (int i = 1; i < lines.Length; i++) {
         line = lines[i];
         fields = line.Split(',');
         cmd.Parameters["@IP"].Value = fields[0];
         cmd.Parameters["@Upstream"].Value = fields[4];
         cmd.Parameters["@Downstream"].Value = fields[5];
         cmd.ExecuteNonQuery();
      }
   }
}

删除不必要的ToString（）调用[/ Edit ]

Removed unnecessary ToString() invocations [/Edit]

private void button2_Click(object sender, EventArgs e)
{
    string finaloutput = "IP,C1,PktUp,PktDown,Upstream,Downstream,C2,C3,C4,C5\r\n192.168.1.56,0,77776,98364,9203233,96637437,0,0,0,0\r\n192.168.1.12,0,80718,105922,11845169,94299988,0,0,0,0";
    string[] lines = finaloutput.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);

    string line;
    string[] fields;

    using (SqlConnection con = new SqlConnection(@"Data Source='localhost';Initial Catalog='NetUsage';User ID='sa';Password='tstc123'"))
    {

        con.Open();

        using (SqlCommand cmd = new SqlCommand("INSERT INTO csv (IP, Upstream, Downstream) VALUES (@IP, @Upstream, @Downstream)", con))
        {

            //cmd.Parameters.Add("@IP");
            //cmd.Parameters.Add("@Upstream");
            //cmd.Parameters.Add("@Downstream");
            cmd.Parameters.Add("@IP", SqlDbType.VarChar);
            cmd.Parameters.Add("@Upstream", SqlDbType.Float);
            cmd.Parameters.Add("@Downstream", SqlDbType.Float);


            // We start at index 1 as the first row (index 0) seems to hold column headers
            for (int i = 1; i < lines.Length; i++)
            {
                line = lines[i];
                fields = line.Split(',');
                cmd.Parameters["@IP"].Value = fields[0];
                cmd.Parameters["@Upstream"].Value = fields[4];
                cmd.Parameters["@Downstream"].Value = fields[5];
                cmd.ExecuteNonQuery();

            }
            MessageBox.Show("Successfull!");
        }
    }
}

您需要分两个阶段拆分输入。首先拆分\\\\ n，然后在每一行拆分，。按照以下方法修改您的代码。

You need to split the input in two phases. First split over "\r\n" and then on each row split over ",". Modify your code as per below approach.

string[] rows = finaloutput.Split(new string[] { "\r\n" }, StringSplitOptions.None);

foreach(string srow in rows)
{
string[] fields = srow.Split(',');
    using (SqlConnection con = new SqlConnection(@"Data Source='localhost';Initial Catalog='NetUsage';User ID='sa';Password='tstc123'"))
    {
 
        con.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO usage(IP, Upstream, Downstream) VALUES (@IP, @Upstream, @Downstream)", con);
        cmd.Parameters.AddWithValue("@IP", fields[0].ToString());
        cmd.Parameters.AddWithValue("@Upstream", fields[4].ToString());
        cmd.Parameters.AddWithValue("@Downstream", fields[5].ToString());
        cmd.ExecuteNonQuery();
    }
}

这篇关于如何在C＃中将数据插入MS Sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！