Tjek Database om værdi eksisterer ellers indsæt
Hejsa,Jeg har en windowsform hvor jeg vil gemme noget metadata.
I den vil jeg så gerne tjekke i min metadatatable om den eksisterer i forvejen og hvis dne gør, så skal den ikke gøre noget. Hvis den ikke eksisterer skal den gemme den.
Jeg har prøvet følgende men kan ikke få det til at virke og det er som om den looper og hver enkelt værdi jeg har i min metatable.
private void Savelistboxbtn_Click(object sender, EventArgs e)
{
string conn = "Server=localhost;Initial Catalog=MetaData;Integrated Security=SSPI;";
string querysequence = "select NEXT VALUE FOR Metadata.dbo.MetaIDbiml as MetaID";
SqlConnection connDatabase = new SqlConnection(conn);
connDatabase.Open();
SqlCommand cmdseq = new SqlCommand(querysequence, connDatabase);
SqlDataReader myReader;
myReader = cmdseq.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
int MetaID = (int)(dt.Rows[0][0]);
connDatabase.Close();
List<String> columnData = new List<String>();
using (SqlConnection connection = new SqlConnection("Server=localhost;Initial Catalog=MetaData;Integrated Security=SSPI;"))
{
connection.Open();
string query = "select distinct SourceTable from metadata.dbo.BimlMetaTable";
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
columnData.Add(reader.GetString(0));
}
}
}
}
if (columnData.Count == 0)
{
for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++) //Indexing(counting) the numbers of checkeditems
{
connDatabase.Open();
string query = "insert into dbo.BimlMetaTable (MetaID,SourceDB,SourceTable,DestinationDB,DestinationTable) values ('" + MetaID + "','" + cmb.Text + "',@sourcetable,'" + cmbdest.Text + "',@sourcetable)";
SqlCommand cmd = new SqlCommand(query, connDatabase);
cmd.Parameters.Add("@sourcetable", SqlDbType.NVarChar).Value = checkedListBox1.CheckedItems[i]; //Adding the selected items to parameter @check
cmd.ExecuteNonQuery();
connDatabase.Close();
MessageBox.Show(checkedListBox1.CheckedItems.Count + " : Tables has been added");
Form2 frm = new Form2(cmb.Text);
frm.Show();
//Closing the first form to go the next form
//this.Hide();
//var form2 = new Form2();
//form2.Closed += (Savelistboxbtn_Click, args) => this.Close();
//form2.Show();
}
}
else
{
foreach (string s in columnData)
{
//for (int iis = 0; iis < columnData.Count; iis++) // Loop through List with for
//{
if (s.Equals(checkedListBox1.CheckedItems) || s.Equals(null) || s.Equals(""))
{
MessageBox.Show(checkedListBox1.CheckedItems + " has already been added");
}
else
{
for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++) //Indexing(counting) the numbers of checkeditems
{
connDatabase.Open();
string query = "insert into dbo.BimlMetaTable (MetaID,SourceDB,SourceTable,DestinationDB,DestinationTable) values ('" + MetaID + "','" + cmb.Text + "',@sourcetable,'" + cmbdest.Text + "',@sourcetable)";
SqlCommand cmd = new SqlCommand(query, connDatabase);
cmd.Parameters.Add("@sourcetable", SqlDbType.NVarChar).Value = checkedListBox1.CheckedItems[i]; //Adding the selected items to parameter @check
cmd.ExecuteNonQuery();
connDatabase.Close();
MessageBox.Show(checkedListBox1.CheckedItems.Count + " : Tables has been added");
Form2 frm = new Form2(cmb.Text);
frm.Show();
//Closing the first form to go the next form
//this.Hide();
//var form2 = new Form2();
//form2.Closed += (Savelistboxbtn_Click, args) => this.Close();
//form2.Show();
}
}
}
}
}
