C# 仓库物料管理系统连接数据库SQL 高级语言编程

╰+攻爆jí腚メ 2023-02-21 04:58 10阅读 0赞

(1)系统功能包括 ：产品入出库登记、确认入出库信息、删除库内信息、领出信息登记、产品分类管理、仓库设置。

(2)用户功能包括：查询库内信息、查询出库信息、查询入库信息、支持多格式查询信息导出、支持多格式信息批量导入、修改本用户密码。

功能图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70][]

**登陆模块**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 1][]

**功能管理模块**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 2][]

**登陆界面**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 3][]

**注册界面**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 4][]

主界面

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 5][]

**菜单**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 7][]

数据分析

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 8][]

**数据库部分设计:参考** [https://blog.csdn.net/qq\_40953393/article/details/101375142][https_blog.csdn.net_qq_40953393_article_details_101375142]

e-r图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 9][]

部分代码:

饼状统计图绘制

//绘制饼状图************************
            /// <summary>
            /// 计算数值综合
            /// </summary>
            /// <param name="Value"></param>
            /// <returns></returns>
            private static decimal Sum(decimal[] Value)
            {
                decimal t = 0;
                foreach (decimal d in Value)
                {
                    t += d;
                }
                return t;
            }
            /// <summary>
            /// 计算各项比例
            /// </summary>
            /// <param name="Value"></param>
            /// <returns></returns>
            private static decimal[] GetItemRate(decimal[] Value)
            {
                decimal sum = Sum(Value);
                decimal[] ItemRate = new decimal[Value.Length];
                for (int i = 0; i < Value.Length; i++)
                {
                    ItemRate[i] = Value[i] / sum;
                }
                return ItemRate;
            }
            /// <summary>
            /// 根据比例，计算各项角度值
            /// </summary>
            /// <param name="ItemRate"></param>
            /// <returns></returns>
            private static int[] GetItemAngle(decimal[] ItemRate)
            {
                int[] ItemAngel = new int[ItemRate.Length];
                for (int i = 0; i < ItemRate.Length; i++)
                {
                    decimal t = 360 * ItemRate[i];
                    ItemAngel[i] = Convert.ToInt32(t);
                }
                return ItemAngel;
            }
            /// <summary>
            /// 绘制饼图(主要是分析不同类型的数值所占比例)，参数有图的标题，项目名称，项目的数值，名称和数值都是长度对应的
            /// </summary>
            /// <param name="Title">图的标题</param>
            /// <param name="ItemName">项目名称</param>
            /// <param name="ItemValue">项目的数值</param>
            /// <returns>Bitmap图形</returns>
            public static Bitmap GetPieImage(string Title, string[] ItemName, decimal[] ItemValue)
            {
                Bitmap image = GenerateImage(Title);
                //
                //主区域图形
                Rectangle RMain = new Rectangle(40, 80, 380, 300);
                //图例信息
                Rectangle RDes = new Rectangle(550, 90, 10, 10);
                Font f = new Font("新宋体", 9, FontStyle.Bold);
                Graphics g = Graphics.FromImage(image);
                g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
                g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
                try
                {
                    //分析数据，绘制饼图和图例说明
                    decimal[] ItemRate = GetItemRate(ItemValue);
                    int[] ItemAngle = GetItemAngle(ItemRate);
                    int Angle1 = 0;
                    int Angle2 = 0;
                    int len = ItemValue.Length;
                    Color c = new Color();
                    //3D
                    g.DrawPie(new Pen(Color.Black), RMain, 0F, 360F);
                    g.DrawPie(new Pen(Color.Black), new Rectangle(RMain.X, RMain.Y + 10, RMain.Width, RMain.Height), 0F, 360F);
                    g.FillPie(new SolidBrush(Color.Black), new Rectangle(RMain.X, RMain.Y + 10, RMain.Width, RMain.Height), 0F, 360F);
                    //绘制
                    for (int i = 0; i < len; i++)
                    {
                        Angle2 = ItemAngle[i];
                        //if (c != GetRandomColor(i))
                        c = GetRandomColor(i);
                        SolidBrush brush = new SolidBrush(c);
                        string DesStr = ItemName[i] + "(" + (ItemRate[i] * 100).ToString(".00") + "%" + ")" + ItemValue[i].ToString(".00");
                        //
                        DrawPie(image, RMain, c, Angle1, Angle2);
                        Angle1 += Angle2;
                        DrawDes(image, RDes, c, DesStr, f, i);
                    }
                    return image;
                }
                finally
                {
                    g.Dispose();
                }
            }

效果如上面的图!!!

数据保存及其转换:

datatable转txt datatable数据转为excel txt或excel转DataSet

//datatable数据保存到txt
            public void SavaTxt(DataTable dt, string name)
            {
                StreamWriter sw = new StreamWriter(name);
                string col_txt = "";
                string row_txt = "";
                foreach (DataColumn item in dt.Columns)// dt为DataTable      
                {
                    col_txt += item.ToString() + "       "; // 循环得到列名          
                }
                col_txt = col_txt.Substring(0, col_txt.Length - 1);
                sw.WriteLine(col_txt);  //写入文件                 
                foreach (DataRow item in dt.Rows)
                {
                    row_txt = ""; //此处容易遗漏，导致数据的重复添加           
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        row_txt += item[i].ToString() + "       "; //循环得到行数据  
                    }
                    row_txt = row_txt.Substring(0, row_txt.Length - 1);
                    sw.WriteLine(row_txt, Encoding.UTF8);//写入文件
                }
                sw.Close();
            }
    
     //datatable数据转为excel
            public void SavaXls(Workbook workbook, string FileName = "")
            {
                if (!string.IsNullOrEmpty(FileName))
                {
                    workbook.SaveToFile(FileName, ExcelVersion.Version2010);
                }
                else
                {
                    workbook.SaveToFile(DateTime.Now.ToString("yyyyMMddhhmmssfff"), ExcelVersion.Version2010);
                }
            }
    
    //读取txt文本内容
            public string ReadTxtFile(string Path)
            {
                StreamReader sr = new StreamReader(Path);
                string str = "";
                while (!sr.EndOfStream)
                {
                    str = sr.ReadLine().ToString();//读取每行数据
    
                }
                sr.Close();
                return str;
            }
    
            //获取text文件或excel数据 转为DataSet 类型
            public DataSet getData()
            {
                //判断文件后缀
                var path = file.FileName;
                string fileSuffix = System.IO.Path.GetExtension(path);
                if (string.IsNullOrEmpty(fileSuffix))
                    return null;
                else if (fileSuffix == ".txt")//txt数据转为datatable
                {
                    DataSet ds = new DataSet();
                    // 读出文本文件的所有行
                    string[] lines = File.ReadAllLines(path, Encoding.Default);
                    DataTable dt = new DataTable();
                    Regex.Split(lines[0], @"\s+").Where(t => t.Trim() != "").ToList().ForEach(t => dt.Columns.Add(t.Trim()));
                    for (int i = 1; i < lines.Length; i++)
                    {
                        DataRow dr = dt.NewRow();
                        Regex.Split(lines[i], @"\s+").Where(t => t.Trim() != "").Select((t, index) => dr[index] = t).ToArray();
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                    return ds;
                }
                else//excel数据转为datatable
                {
                    using (DataSet ds = new DataSet())
                    {
                        //判断Excel文件是2003版本还是2007版本
                        string connString = "";
                        if (fileSuffix == ".xls")
                            connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                        else
                            connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                        //读取excel文件
                        string sql_select = " SELECT * FROM [Sheet1$]";
                        using (OleDbConnection conn = new OleDbConnection(connString))
                        using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
                        {
                            conn.Open();
                            cmd.Fill(ds);
                        }
                        if (ds == null || ds.Tables.Count <= 0) return null;
                        return ds;
                    }
                }
            }

数据库相关操作:增删查改

//数据库查询方法
            public DataSet select(string select)
            {
                try
                {
                    SqlConnection sqlconn = new SqlConnection(SQLstr);
                    SqlDataAdapter sqlda = new SqlDataAdapter(select, sqlconn);
                    DataSet dset = new DataSet();
                    sqlda.Fill(dset);
                    return dset;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "警告");
                    return null;
                }
            }
    
            //数据库语句查询返回查询结果 ，字符串形式输出 
            //selectSQL 查询语句   name列名
            public string select(string selectSQL,string name)
            {
                try
                {
                    string s;
                    SqlConnection sqlconn = new SqlConnection(SQLstr);
                    SqlDataAdapter sqlda = new SqlDataAdapter(selectSQL, sqlconn);
                    DataSet dset = new DataSet();
                    sqlconn.Open();
                    sqlda.Fill(dset);
                    s = dset.Tables[0].Rows[0][name].ToString().Trim();
                    sqlconn.Close();
                    return s;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "警告");
                    return null;
                }
            }
    
            //数据库语句操作方法
            public void tranSQL(string str)
            {
                try
                {
                    SqlConnection sqlconn = new SqlConnection(SQLstr);
                    SqlCommand sqlcomm = new SqlCommand(str, sqlconn);
                    sqlconn.Open();
                    sqlcomm.ExecuteNonQuery();
                    sqlconn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "警告");
                }
            }
    
            //读取数据库数据
            public SqlDataReader readSQL(string str)
            {
                
                try
                {
                    SqlConnection sqlconn = new SqlConnection(SQLstr);
                    SqlCommand sqlcomm = new SqlCommand(str, sqlconn);
                    sqlconn.Open();
                    SqlDataReader sdr = sqlcomm.ExecuteReader();
                    return sdr;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "警告");
                    return null;
                }
            }
    
            //判断数据库重复真重复 假不存在
            public bool judgeCopy(string sdrSQL,string strSQL,string strCON)
            {
                bool Flag = false;
                SqlDataReader reader =readSQL(sdrSQL);
                while (reader.Read())
                {
                    if (strCON == reader[strSQL].ToString().Trim())
                    {
                        Flag = true;
                        break;
                    }
                    else
                    {
                        Flag = false;
                    }
                }
                reader.Close();
                return Flag;
            }
    
            //数据库某一列数据存入字符数组
            //sqlStr 数据库语句 rowStr列名
            public string[] imgeNeedstring(string sqlStr,string rowStr)
            {
                DataTable dt = select(sqlStr).Tables[0];
                //string dt = select(sqlStr,rowStr);
                string[] abc = new string[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string row = select(sqlStr).Tables[0].Rows[i][rowStr].ToString().Trim();
                    abc[i] = row.ToString().Trim();
                }
                return abc;
            }
    
    
            //字符数组转decimal类型
            public Decimal[] imgeDecimal(string sqlStr, string rowStr)
            {
                string[] Content = imgeNeedstring(sqlStr,rowStr);
                Decimal[] d = new Decimal[Content.Length];
                for (int i = 0; i < Content.Length; i++)
                {
                    d[i] = Convert.ToInt32(Content[i].ToString().Trim());
                }
                return d;
            }
    
            ///<summary>
            ///附加数据库
            ///</summary>
            public void AttachDB()
            {
                SqlConnection conn = new SqlConnection("server = localhost; database = master; integrated security = SSPI");
                SqlCommand  comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "select count(*) from sys.databases where name = 'WNA'";
                int a = Convert.ToInt32(comm.ExecuteScalar());
                if (a == 1)
                {
                    return;
                }
                else
                {
                    try
                    {
                        string Path = @"../../obj/Debug/DataBase/";
                        FileInfo file = new FileInfo(Path);
                        string mdfPath = file.FullName + "WNMData.mdf";//mdf文件的路径
                        string ldfPath = file.FullName + "WNMData.ldf";
                        comm.CommandText = "sp_attach_db";//系统数据库master 中的一个附加数据库存储过程。
                        comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
                        comm.Parameters[@"dbname"].Value = "WNM";
                        comm.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));  //一个主文件mdf，一个或者多个日志文件ldf，或次要文件ndf
                        comm.Parameters[@"filename1"].Value = mdfPath;
                        comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
                        comm.Parameters[@"filename2"].Value = ldfPath;
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.ExecuteNonQuery();
    
                       //MessageBox.Show("附加数据库成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch //(Exception ex)
                    {
                       // MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190925174734771.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190925174757794.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190925174819905.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190925174852698.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190925174909503.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190925174939979.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190925174955519.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20190925175003299.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20190925175023660.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_40953393_article_details_101375142]: https://blog.csdn.net/qq_40953393/article/details/101375142
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20190925175142392.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTUzMzkz,size_16,color_FFFFFF,t_70