insert multi textbox value to sql table

Question

User-807418713 posted

Hello

My Textbox look like this

On Button Click C# I want to insert in sql table like this Table1:

1

2

3

Answer 1

User475983607 posted

Hello

My Textbox look like this

On Button Click C# I want to insert in sql table like this Table1:

1

2

3

Split the string by the new line character to create an array.  Then loop over the array to insert the values.  Or build dynamic SQL to INSERT the value in one script.

However, this is a very brittle design.

Answer 2

User665608656 posted

Hi Gopi.MCA,

According to your requirements, as mgebhard said, you can get the text content in code behind, the string is then divided into arrays based on newline characters.

When you get this array, I suggest you use SqlBulkCopy to insert data into your table1.

For more details, you could refer to the following code:

protected void Button1_Click(object sender, EventArgs e)
        {
            string[] striparr = new string[] { };
            if (!string.IsNullOrEmpty(TextBox1.Text.ToString()))//Judgment is not empty
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                conn.Open();
                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn);
                sqlbulkcopy.BulkCopyTimeout = 100;  //The number of seconds allowed to complete the operation before the timeout
                sqlbulkcopy.BatchSize = striparr.Length;  //Number of rows per batch
                sqlbulkcopy.DestinationTableName = "Table1";  //Name of target table on server
                sqlbulkcopy.ColumnMappings.Add(0, "Name");//Mapping defines the relationship between columns in the data source and columns in the target table
                striparr = TextBox1.Text.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None);
                DataTable dt = new DataTable();
                dt.Columns.Add("Name");
                for (int i = 0; i < striparr.Length; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = striparr[i].ToString();
                    dt.Rows.Add(dr); 
                }
                sqlbulkcopy.WriteToServer(dt);  // Upload Data Table Data to Data Table
                conn.Close();
            }
        }

This is the structure of my table1:

CREATE TABLE [dbo].[Table1] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

The result of my work demo:

Best Regards,

YongQing.