如何在C＃中将数据插入MS Sql数据库

问题描述

我有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 

推荐答案

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!");
        }
    }
}

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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！