public List <Personer> GetPersonerWithDataSet(out string errormsg) { SqlConnection dbConnection = new SqlConnection(); dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True"; String sqlstring = "SELECT * FROM Personer"; SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection); SqlDataAdapter myAdapter = new SqlDataAdapter(dbCommand); DataSet myDS = new DataSet(); List <Personer> PersonLista = new List <Personer>(); try { dbConnection.Open(); myAdapter.Fill(myDS, "myFisk"); int count = 0; int i = 0; count = myDS.Tables["myFisk"].Rows.Count; if (count > 0) { while (i < count) { Personer pd = new Personer(); pd.Persnr = Convert.ToInt64(myDS.Tables["myFisk"].Rows[i]["Persnr"]); pd.Namn = myDS.Tables["myFisk"].Rows[i]["Namn"].ToString(); pd.Telnr = myDS.Tables["myFisk"].Rows[i]["Telnr"].ToString(); i++; PersonLista.Add(pd); } errormsg = ""; return(PersonLista); } else { errormsg = "Det hämtas ingen Person!"; return(null); } } catch (Exception e) { errormsg = e.Message; return(null); } finally { dbConnection.Close(); } }
public int UpdatePersoner(Personer p1, int Pers_id, out string errormsg) { SqlConnection dbConnection = new SqlConnection(); dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True"; string sqlstring = "UPDATE Personer SET Persnr = @persnr, Namn = @namn, Telnr = @telnr WHERE Persnr = @id"; SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection); dbCommand.Parameters.Add("persnr", SqlDbType.NVarChar, 50).Value = p1.Persnr; dbCommand.Parameters.Add("namn", SqlDbType.NVarChar, 50).Value = p1.Namn; dbCommand.Parameters.Add("telnr", SqlDbType.NVarChar, 50).Value = p1.Telnr; dbCommand.Parameters.Add("id", SqlDbType.Int).Value = Pers_id; try { dbConnection.Open(); int i = 0; i = dbCommand.ExecuteNonQuery(); if (i == 1) { errormsg = ""; } else { errormsg = "Gick ej att uppdatera person"; } return(i); } catch (Exception e) { errormsg = e.Message; return(0); } finally { dbConnection.Close(); } }
public int InsertPerson(Personer p1, out string errormsg) { SqlConnection dbConnection = new SqlConnection(); dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True"; string sqlstring = "INSERT INTO Personer (Persnr, Namn, Telnr) VALUES (@persnr, @namn, @telnr)"; SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection); dbCommand.Parameters.Add("persnr", SqlDbType.BigInt).Value = p1.Persnr; dbCommand.Parameters.Add("namn", SqlDbType.NVarChar, 50).Value = p1.Namn; dbCommand.Parameters.Add("telnr", SqlDbType.NVarChar, 15).Value = p1.Telnr; try { dbConnection.Open(); int i = 0; i = dbCommand.ExecuteNonQuery(); if (i == 1) { errormsg = ""; } else { errormsg = "Det skapas ej en person i databasen"; } return(i); } catch (Exception e) { errormsg = e.Message; return(0); } finally { dbConnection.Close(); } }