How to Store List of Data from Database to Arraylist or List in C#

Insert arraylist into database C# webservice

First of all you need to change the Query to Parameterized Query, since it is prone to SQL Injection. and then you can split the string coming with , and run a loop to get the values in the DB.

public void saverecd(string id, string particular,string amt,string tdate, string total, string date, string utrno, string modeofpayment, string transferdate,string trainer, string typeofadj)
{
    List<string> sp = particular.split(',');
    int i = 0;
    foreach(string s in sp)
    {
        using (SqlConnection connection = new SqlConnection(/* connection info */))
        {
            sqlq = "insert into finalinstructoreexpense(sonvinid,particulars,amount,totalamt,date,utno,paymentid,paymode,issuedate,sondate,trainer,type,bank_id) values(@id,@s,@amt,@total,@dt,@utrno,@paymentid,@modeofpayment,@transferdate,@tdate,@trainer,@typeofadj,null)";
            connection.Open();
            using (SqlCommand comm1 = new SqlCommand(sql, connection))
            {
                comm1.Parameters.Add("@id",SqlDbType.Int).value=id;
                comm1.Parameters.Add("@s",SqlDbType.Varchar, 50).value =s;
                comm1.Parameters.Add("@amt",SqlDbType.Varchar, 50).value =amt.Split(',')[i];;
                comm1.Parameters.Add("@trainer",SqlDbType.Varchar, 50).value =trainer;
                comm1.Parameters.Add("@dt", SqlDbType.Date).Value = date;
                //You can add all your Parameters here
                // I have added 3 types of as Int, Varchar and Date to give the idea
                comm1.ExecuteNonQuery();
                message = "Adjusted Amount Inserted Successfully";
                Context.Response.Write(message);
                i++;
            }
        }
    }
}

Storing .Net ArrayList in database

This is probably part of a one-to-many relationship. Say for example your ArrayList is storing strings, then create a separate table, where one of its fields in this string value. You can send the ArrayList as a param to your DALC class and loop through it there, adding one record at a time to the DB by calling a stored proceedure, etc.

Trying to save array of data in database using c#

I think you want to save multiple WireDimDetail to Prop_detail per user

so a simple way is loop through the wireDimDetails and get string of wireDimDetail for inserting in Tdc_property.

After inserting row in TDC_PRODUCT1, for inserting details the code would be like:

[WebMethod]
public static void SaveFrmDetails(User user)
{
    string connectionString = ConfigurationManager.ConnectionStrings["conndbprodnew"].ConnectionString;
    using (OracleConnection con = new OracleConnection(connectionString))
    {
        using (OracleCommand cmd = new OracleCommand("INSERT INTO TDC_PRODUCT1(PRODUCT_ID,TDC_NO, REVISION) VALUES (:PRODUCT_ID,:TDC_NO,:REVISION )", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue(":PRODUCT_ID", user.PRODUCT_ID);
            cmd.Parameters.AddWithValue(":TDC_NO", user.TDC_NO);
            cmd.Parameters.AddWithValue(":REVISION", user.REVISION);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert into Prop_detail(Tdc_no,Tdc_property) values(@tdN,@tdProp)";
            foreach (WireDimDetail wireDimDetail in user.WireDimDetails)
            {
                cmd.Parameters.Clear();
                var stringwriter = new System.IO.StringWriter();
                var serializer = new System.Xml.Serialization.XmlSerializer(wireDimDetail.GetType());
                serializer.Serialize(stringwriter, wireDimDetail);

                cmd.Parameters.AddWithValue("@tdN", user.TDC_NO);
                cmd.Parameters.AddWithValue("@tdProp", stringwriter.ToString());
                cmd.ExecuteNonQuery();
            }

            con.Close();
        }
    }
}

the stringwriter is string of each WireDimDetail.

but if you want to new record for sizeMin,sizeMax,tolMin and tolMax change foreach loop to:

foreach (WireDimDetail wireDimDetail in user.WireDimDetails)
{
    cmd.Parameters.Clear();
    string[] strNumbers = new string[4]
    {
        wireDimDetail.SizeMin,
        !string.IsNullOrEmpty(wireDimDetail.SizeMax) ? wireDimDetail.SizeMax : "0",
        wireDimDetail.TolMin,
        wireDimDetail.TolMax
    };
    foreach (string number in strNumbers)
    {
        cmd.Parameters.AddWithValue("@tdN", user.TDC_NO);
        cmd.Parameters.AddWithValue("@tdProp", number);
        cmd.ExecuteNonQuery();
    }
}

EDIT:

The final try would be like :

[WebMethod]
public static void SaveFrmDetails(User user)
{
    string connectionString = ConfigurationManager.ConnectionStrings["conndbprodnew"].ConnectionString;
    using (OracleConnection con = new OracleConnection(connectionString))
    {
        using (OracleCommand cmd = new OracleCommand("INSERT INTO TDC_PRODUCT1(PRODUCT_ID,TDC_NO, REVISION) VALUES (:PRODUCT_ID,:TDC_NO,:REVISION )", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue(":PRODUCT_ID", user.PRODUCT_ID);
            cmd.Parameters.AddWithValue(":TDC_NO", user.TDC_NO);
            cmd.Parameters.AddWithValue(":REVISION", user.REVISION);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert into Prop_detail(Tdc_no,Rowno,Prop_Name,Tdc_property) values(@tdN,@rowNo,@propN,@tdProp)";
            int rowNum = 1;// You can get rowNum from DB and initiate it to last rowNum
            foreach (WireDimDetail wireDimDetail in user.WireDimDetails)
            {
                cmd.Parameters.Clear();
                Dictionary<string, string> strNumbers = new Dictionary<string, int>()
                    {
                    {"sizMin",  wireDimDetail.SizeMin },
                    {"sizeMax" , !string.IsNullOrEmpty(wireDimDetail.SizeMax) ? wireDimDetail.SizeMax.ToString() : "0" },
                    {"tolMin",   wireDimDetail.TolMin.ToString() },
                    {"tolMax",   wireDimDetail.TolMax.ToString() }
                    }; 
                cmd.Parameters.Clear();

                foreach (KeyValuePair<string, string> kvp in strNumbers)
                {
                    cmd.Parameters.AddWithValue("@tdN", user.TDC_NO);
                    cmd.Parameters.AddWithValue("@rowNo", rowNum);
                    cmd.Parameters.AddWithValue("@propN", kvp.Key);
                    cmd.Parameters.AddWithValue("@tdProp", kvp.Value);
                    cmd.ExecuteNonQuery();

                }
                rowNum++;
            }
            con.Close();
        }
    }
}

NOTES:

1- rowNum can be retrieved from DB and then increase (can be initiated by select max(rowNo) from Prop_detail).

2- I used dictionary to hold the name of variable such as sizeMax and etc, there are many ways to do this

---

Related Topics