Retrieve and Save (Insert) Array of String to database in C# and VB.Net in ASP.Net

Last Reply one month ago By Adnaan

Posted one month ago

i have seen example from 

Save (Insert) Array of String to database in C# and VB.Net

but it shows array string that is not from database. can u give example that array is retrieve from database using stored procedure and save it back to database using stored procedure. MY code are as below.

maybe my foreach is not put at the right place. Please help

 

Posted one month ago

Hi Hazel,

Refer below code.

Code

C#

protected void Button1_Click(object sender, EventArgs e)
{
    string[] nm = GetIds().ToArray();
    foreach (string name in nm)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Names(Name) VALUES(@Name)"))
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@Name", name);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

private List<string> GetIds()
{
    List<string> ids = new List<string>();
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT Name FROM Customers";
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand(query);
        con.Open();
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            ids.Add(sdr["Name"].ToString());
        }
        con.Close();
    }
    return ids;
}

VB.Net

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim nm As String() = GetIds().ToArray()

        For Each name As String In nm
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

            Using con As SqlConnection = New SqlConnection(constr)

                Using cmd As SqlCommand = New SqlCommand("INSERT INTO Names(Name) VALUES(@Name)")
                    cmd.Connection = con
                    cmd.Parameters.AddWithValue("@Name", name)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        Next
    End Sub

    Private Function GetIds() As List(Of String)
        Dim ids As List(Of String) = New List(Of String)()
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT Name FROM Customers"

        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand(query)
            con.Open()
            Dim sdr As SqlDataReader = cmd.ExecuteReader()

            While sdr.Read()
                ids.Add(sdr("Name").ToString())
            End While

            con.Close()
        End Using

        Return ids
    End Function